Daylights savings time problem

cjuarez

New Member
Joined
Jan 21, 2005
Messages
1
I have a project where I enter the date in a cell, in mm/dd/yyyy format. From there I want the number of days in that month to appear. This workbook will have as many sheets worksheets as the days in the month.

So, the two things I would like to know are:
1) How do I get the number of days for that month to appear in another cell

and
2) How do I get the number of hours for that day to appear. That may sounds stupid, but one of these worksheets will be used everyday of the year and I don't want the user to forget that for daylights savings time, one day has 23 hours and the other has 25 hours. A simple IF statement will work, but I don't know how to get it to read "If the date is mm/dd/yyyy".

Thanks
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Welcome to the board!

Days in month =EOMONTH(A1,0)-EOMONTH(A1,-1)

Hours in day =IF(A1=DATEVALUE("10/30/2005"),25,IF(A1=DATEVALUE("4/3/2005"),23,24))
 
Upvote 0
Fyi, the EOMONTH function is a part of the Analysis Toolpak add-in. Tools --> Add-ins --> Analysis Toolpak. An incomplete Excel installation would sometimes render it inaccessible.

Note: You must have at least one workbook opened to access the Add-ins menu option.
 
Upvote 0
If you want to do it without the Analysis Toolpak, your first question could be done like this:

=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))

This one should calculate when US Daylight Savings Time occurs and display the appropriate hours:

=IF((A1=(CEILING((DATE(YEAR(A1),4,0))-1,7)+1)),23,IF(A1=(FLOOR((DATE(YEAR(A1),11,1))-1,7)+1),25,24))
 
Upvote 0

Forum statistics

Threads
1,218,840
Messages
6,144,790
Members
450,567
Latest member
Mplz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top