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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,000
Office Version
  1. 365
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

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,129,592
Messages
5,637,290
Members
416,962
Latest member
samfuge

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
Top