Daylight Savings Time Funcitions :: MrExcel Message Board
 Search   Memberlist   Usergroups   Favorites   Statistics   Register

 Daylight Savings Time Funcitions

mainstreet14534
Welcome to the Board

Joined: 20 Sep 2003
Posts: 2

Flag:

Status: Offline

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!
_________________
----------------------------------------
MainStreet

Sat Sep 20, 2003 5:43 pm

Yogi Anand
MrExcel MVP

Joined: 13 Mar 2002
Posts: 5749
Location: Michigan USA

Status: Offline

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!
_________________
Regards!
Yogi Anand

Sat Sep 20, 2003 6:44 pm

.

Joined: 15 Feb 2002
Posts: 14083
Location: The Hague
Flag:

Status: Offline

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"

Sat Sep 20, 2003 8:24 pm

tbardoni
Board Master

Joined: 30 Aug 2002
Posts: 1613
Location: Rochester, MI USA
Flag:

Status: Offline

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
_________________
Board Nit
Dyslexics of the World Untie!

Sat Sep 20, 2003 10:01 pm

Ivan F Moala
MrExcel MVP

Joined: 11 Feb 2002
Posts: 3211
Location: Auckland, New Zealand
Flag:

Status: Offline

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.

 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)

_________________
Kind Regards,
Ivan F Moala From the City of Sails

Sun Sep 21, 2003 5:06 am

Yogi Anand
MrExcel MVP

Joined: 13 Mar 2002
Posts: 5749
Location: Michigan USA

Status: Offline

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.
_________________
Regards!
Yogi Anand

Sun Sep 21, 2003 3:34 pm

mainstreet14534
Welcome to the Board

Joined: 20 Sep 2003
Posts: 2

Flag:

Status: Offline

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
_________________
----------------------------------------
MainStreet

Mon Sep 22, 2003 1:43 am
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First

Forum Jump:
 Jump to: Select a forum MrExcel Forums----------------Excel QuestionsInternational forumMicrosoft AccessTechnical issues and Future development Holy Macro! Books----------------Holy Macro! Products The Lounge----------------Max Cells Lounge Announcements----------------About This BoardHall of Fame WinnersTest Here

Page 1 of 1

Forum Rules:
 You cannot post new topics in this forumYou cannot reply to topics in this forumYou cannot edit your posts in this forumYou cannot delete your posts in this forumYou cannot vote in polls in this forum