Daylight Savings Time Funcitions

Thanks:  0
Likes:  0

# Thread: Daylight Savings Time Funcitions

1. ## Daylight Savings Time Funcitions

Howdy - First Post.

I have a need for a function or formula to determine if a date should have Daylight Saving Time (DST) applied. (e.g., Test(11/DD/YYYY)=Yes or =1)

I understand the 'rules to be': for areas of the country who observe DST, DST occurs between the 4th Sunday in October to the 4th Sunday in the following April. (The time switch occurs at 2 AM.)

I can obviously determine the applicable month ranges
(e.g., =IF((MONTH(A143)>=10)+(MONTH(A143)<=4),"DST month","")

For me the tricky part has been determining the applicable period corressponding to the 4th Sunday so that I can combine the tests.

I can determine what is the 4th Sunday (a date each month). However, I haven't determined when a month's date is >= the 4th Sunday.

I might be dense, but 'am having trouble.

Any ideas anyone?

Thanks!

BTW - Just bought Mr. Excel on Excel. I'm fairly adept, but it is still worth the investment!

2. ## Re: Daylight Savings Time Funcitions

Hi there MainStreet:

Welcome to MrExcel Board!

Please look at the following simulation ...

******** ******************** ************************************************************************>
 Microsoft Excel - Book4 ___Running: xl97 : OS = Windows 98
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 C2C3C4 =

A
B
C
D
1
MonthNumberFourthSundayOfTheMonthOfCurrentYear
2
101/26/03
3
404/27/03
4
1010/26/03
 Sheet2

[HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Would this do? If I have misunderstood your question -- my apologies!

3. ## Re: Daylight Savings Time Funcitions

=--OR((MONTH(C2)<=4)*(C2<=DATE(YEAR(C2),4,7-WEEKDAY(DATE(YEAR(C2),4,1),2)+22)),(MONTH(C2)>=10)*(C2>=DATE(YEAR(C2),10,7-WEEKDAY(DATE(YEAR(C2),10,1),2)+22)))

which results either in 1 or 0. C2 houses a date to test.

You can format the formula cell as:

[=0]"";[=1]"DST month"

4. ## Re: Daylight Savings Time Funcitions

Yogi,
I'm not very good wit DATE formulas yet...if its not too much trouble, could you give a quick rundown of how your formula works...I can't seem to grasp it.

Thanks,
Todd

5. Basically Yogi's formula is calculating on a base Week day
default of 1 - 7 = Sunday - Saturday using american Dates
(to change to English others =(1&"/"&G2)+0-WEEKDAY(1&"/"&G2)+29)

4th sunday = 4 X 7 + Base Day = 1 +> +29

(B4&"/"&1)+0 coerces the date serial number for the 1st of the Month of the current year (US)

Adding them together yeilds 4th Sunday as a Serial number.

Note: Only applicable for North America as daylight savings is different for different continents eg.

****** content=Excel.Sheet name=ProgId>****** content="Microsoft Excel 9" name=Generator>
 Continent Country Beginning and ending days Africa Egypt Start: Last Friday in April End: Last Thursday in September Namibia Start: First Sunday in September End: First Sunday in April Asia Most states of the former USSR Start: Last Sunday in March End: Last Sunday in October Iraq Start: April 1 End: October 1 Israel (Estimate, Israel decides the dates every year) Start: First Friday in April End: First Friday in September Lebanon, Kirgizstan Start: Last Sunday in March End: Last Sunday in October Mongolia Start: Last Sunday in March End: Last Sunday in September Palestine (Estimate) Start: First Friday on or after 5 April End: First Friday on or after 5 October Syria Start: April 1 End: October 1 Iran Start: the first day of Farvardin End: the first day of Mehr Australasia Australia - South Australia, Victoria, Australian Capital Territory, New South Wales, Lord Howe Island Start: Last Sunday in October (region that encompasses Australia, Tasmania, New Zealand, and other islands in the South Pacific) End: Last Sunday in March Australia - Tasmania Start: First Sunday in October End: Last Sunday in March Fiji Start: First Sunday in November End: Last Sunday in February New Zealand, Chatham Start: First Sunday in October End: First Sunday on or after 5 March Tonga Start: First Saturday in October End: First Saturday on or after 15 April Europe European Union, Russia Start: Last Sunday in March End: Last Sunday in October Greenland Start: First Sunday in April End: Last Sunday in October North America Canada, United States, Mexico, St. Johns, Bahamas, Turks and Caicos Start: First Sunday in April End: Last Sunday in October Cuba Start: April 1 End: Last Sunday in October South America Brazil Start: First Sunday in October End: Last Sunday in February Chile Start: First Sunday on or after 9 October End: First Sunday on or after 9 March Falklands Start: First Sunday on or after 8 September End: First Sunday on or after 6 April Paraguay Start: First Sunday in October End: Last Saturday in February Antarctica Antarctica (same as Chile)

6. ## Re: Daylight Savings Time Funcitions

Hi Ivan:

Thanks for great information beautifully presented.

Hi Todd:

I hope Ivan's explanation will do -- otherwise post back and then let us take it from there.

7. ## Re: Daylight Savings Time Funcitions

Thanks everyone.

A fantastic and 'Elegant' solution!

I'll be using this in Excel for some trans-time zone coordination and will try and adopt it for MS Project as well.

---------------------
Dennis

8. ## Re: Daylight Savings Time Funcitions

=--OR((MONTH(C2)<=4)*(C2<=DATE(YEAR(C2),4,7-WEEKDAY(DATE(YEAR(C2),4,1),2)+22)),(MONTH(C2)>=10)*(C2>=DATE(YEAR(C2),10,7-WEEKDAY(DATE(YEAR(C2),10,1),2)+22)))

which results either in 1 or 0. C2 houses a date to test.

You can format the formula cell as:

[=0]"";[=1]"DST month"
I know that this was posted AGES ago, but I hope that someone can help me, as I am still confused with the explanation of this function. Can someone provide a NEW formula for Daylight Savings in the US for the year 2010. Thanks

9. ## Re: Daylight Savings Time Funcitions

Hi,
All very interesting, but the formula of Yogi's doesn't seem to work for August 2010, it gives the 5th (and last Sunday).
Very curious however what '(1&"/"&G2)' actually does, as the brackets are important.
Hope there is a reply to cortiknee, as I am still looking for a valid formulae, that gives DST from the last Sunday in March to the last Sunday in October.
Thanks
Bob

10. ## Re: Daylight Savings Time Funcitions

If you have a date in A2 this formula will give TRUE if that date falls anywhere in the period from the 2nd Sunday in March to the day before the 1st Sunday in November (US daylight saving)

=MATCH(A2,DATE(YEAR(A2),{1,3,11},{1,15,8})-WEEKDAY(DATE(YEAR(A2),{1,3,11},7)))=2

That will work for any date.

For UK BST (Last Sunday in March to the day before last Sunday in October) you can change to

=MATCH(A2,DATE(YEAR(A2),{1,4,11},1)-WEEKDAY(DATE(YEAR(A2),{1,4,11},7)))=2

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•