Daylights savings time problem

[cjuarez]

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

 

[Oaktree]

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))

 

[Zack Barresse]

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.

 

[Scott Huish]

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))