Daylight Savings Time Funcitions

mainstreet14534

New Member
Joined
Sep 20, 2003
Messages
2
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!
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Hi there MainStreet:

Welcome to MrExcel Board!

Please look at the following simulation ...
Book4
ABCD
1MonthNumberFourthSundayOfTheMonthOfCurrentYear
2101/26/03
3404/27/03
41010/26/03
Sheet2


Would this do? If I have misunderstood your question -- my apologies!
 
Upvote 0
=--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"
 
Upvote 0
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
 
Upvote 0
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.

<META content=Excel.Sheet name=ProgId><META content="Microsoft Excel 9" name=Generator><LINK href="./sht_files/filelist.xml" rel=File-List><STYLE>v\:* {behavior:url(#default#VML);}o\:* {behavior:url(#default#VML);}x\:* {behavior:url(#default#VML);}.shape {behavior:url(#default#VML);}</STYLE><STYLE id="daylight savings times_909_Styles"></STYLE>  <DIV id="daylight savings times_909" align=center x:publishsource="Excel"><TABLE style="TABLE-LAYOUT: fixed; WIDTH: 655pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=872 border=0 x:str><COLGROUP><COL style="WIDTH: 212pt; mso-width-source: userset; mso-width-alt: 10313" width=282><COL style="WIDTH: 180pt; mso-width-source: userset; mso-width-alt: 8777" width=240><COL style="WIDTH: 263pt; mso-width-source: userset; mso-width-alt: 12800" width=350><TBODY><TR style="HEIGHT: 27pt; mso-height-source: userset" height=36><TD class=xl26909 style="WIDTH: 212pt; HEIGHT: 27pt" width=282 height=36>Continent</TD><TD class=xl26909 style="BORDER-LEFT: medium none; WIDTH: 180pt" width=240>Country</TD><TD class=xl26909 style="BORDER-LEFT: medium none; WIDTH: 263pt" width=350>Beginning and ending days</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23909 style="BORDER-TOP: medium none; WIDTH: 212pt; HEIGHT: 25.5pt" width=282 height=34 rowSpan=2>Africa</TD><TD class=xl24909 style="BORDER-TOP: medium none; WIDTH: 180pt" width=240 rowSpan=2>Egypt</TD><TD class=xl23909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt" width=350>Start: <FONT class=font5909>Last Friday in April</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt; HEIGHT: 12.75pt" width=350 height=17>End: <FONT class=font5909>Last Thursday in September</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27909 style="BORDER-TOP: medium none; WIDTH: 212pt; HEIGHT: 25.5pt" width=282 height=34 rowSpan=2> </TD><TD class=xl25909 style="BORDER-TOP: medium none; WIDTH: 180pt" width=240 rowSpan=2>Namibia</TD><TD class=xl22909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt" width=350>Start: <FONT class=font5909>First Sunday in September</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt; HEIGHT: 12.75pt" width=350 height=17>End: <FONT class=font5909>First Sunday in April</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23909 style="BORDER-TOP: medium none; WIDTH: 212pt; HEIGHT: 25.5pt" width=282 height=34 rowSpan=2>Asia</TD><TD class=xl24909 style="BORDER-TOP: medium none; WIDTH: 180pt" width=240 rowSpan=2>Most states of the former USSR</TD><TD class=xl23909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt" width=350>Start: <FONT class=font5909>Last Sunday in March</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt; HEIGHT: 12.75pt" width=350 height=17>End: <FONT class=font5909>Last Sunday in October</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27909 style="BORDER-TOP: medium none; WIDTH: 212pt; HEIGHT: 25.5pt" width=282 height=34 rowSpan=2> </TD><TD class=xl25909 style="BORDER-TOP: medium none; WIDTH: 180pt" width=240 rowSpan=2>Iraq</TD><TD class=xl22909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt" width=350>Start: <FONT class=font5909>April 1</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt; HEIGHT: 12.75pt" width=350 height=17>End: <FONT class=font5909>October 1</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24909 style="BORDER-TOP: medium none; WIDTH: 212pt; HEIGHT: 38.25pt" width=282 height=51 rowSpan=3> </TD><TD class=xl24909 style="BORDER-TOP: medium none; WIDTH: 180pt" width=240 rowSpan=3>Israel</TD><TD class=xl24909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt" width=350>(Estimate, Israel decides the dates every year)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt; HEIGHT: 12.75pt" width=350 height=17>Start: <FONT class=font5909>First Friday in April</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt; HEIGHT: 12.75pt" width=350 height=17>End: <FONT class=font5909>First Friday in September</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27909 style="BORDER-TOP: medium none; WIDTH: 212pt; HEIGHT: 25.5pt" width=282 height=34 rowSpan=2> </TD><TD class=xl25909 style="BORDER-TOP: medium none; WIDTH: 180pt" width=240 rowSpan=2>Lebanon, Kirgizstan</TD><TD class=xl22909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt" width=350>Start: <FONT class=font5909>Last Sunday in March</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt; HEIGHT: 12.75pt" width=350 height=17>End:<FONT class=font5909> Last Sunday in October</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24909 style="BORDER-TOP: medium none; WIDTH: 212pt; HEIGHT: 25.5pt" width=282 height=34 rowSpan=2> </TD><TD class=xl24909 style="BORDER-TOP: medium none; WIDTH: 180pt" width=240 rowSpan=2>Mongolia</TD><TD class=xl23909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt" width=350>Start: <FONT class=font5909>Last Sunday in March</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt; HEIGHT: 12.75pt" width=350 height=17>End: <FONT class=font5909>Last Sunday in September</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25909 style="BORDER-TOP: medium none; WIDTH: 212pt; HEIGHT: 38.25pt" width=282 height=51 rowSpan=3> </TD><TD class=xl25909 style="BORDER-TOP: medium none; WIDTH: 180pt" width=240 rowSpan=3>Palestine</TD><TD class=xl25909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt" width=350>(Estimate)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt; HEIGHT: 12.75pt" width=350 height=17>Start: <FONT class=font5909>First Friday on or after 5 April</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt; HEIGHT: 12.75pt" width=350 height=17>End: <FONT class=font5909>First Friday on or after 5 October</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24909 style="BORDER-TOP: medium none; WIDTH: 212pt; HEIGHT: 25.5pt" width=282 height=34 rowSpan=2> </TD><TD class=xl24909 style="BORDER-TOP: medium none; WIDTH: 180pt" width=240 rowSpan=2>Syria</TD><TD class=xl23909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt" width=350>Start: <FONT class=font5909>April 1</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt; HEIGHT: 12.75pt" width=350 height=17>End: <FONT class=font5909>October 1</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27909 style="BORDER-TOP: medium none; WIDTH: 212pt; HEIGHT: 25.5pt" width=282 height=34 rowSpan=2> </TD><TD class=xl25909 style="BORDER-TOP: medium none; WIDTH: 180pt" width=240 rowSpan=2>Iran</TD><TD class=xl22909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt" width=350>Start:<FONT class=font5909> the first day of Farvardin</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt; HEIGHT: 12.75pt" width=350 height=17>End:<FONT class=font5909> the first day of Mehr </FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23909 style="BORDER-TOP: medium none; WIDTH: 212pt; HEIGHT: 12.75pt" width=282 height=17>Australasia</TD><TD class=xl24909 style="BORDER-TOP: medium none; WIDTH: 180pt" width=240 rowSpan=2>Australia - South Australia, Victoria, Australian Capital Territory, New South Wales, Lord Howe Island</TD><TD class=xl23909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt" width=350>Start: <FONT class=font5909>Last Sunday in October</FONT></TD></TR><TR style="HEIGHT: 38.25pt" height=51><TD class=xl24909 style="BORDER-TOP: medium none; WIDTH: 212pt; HEIGHT: 38.25pt" width=282 height=51>(region that encompasses Australia, Tasmania, New Zealand, and other islands in the South Pacific)</TD><TD class=xl23909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt" width=350>End: <FONT class=font5909>Last Sunday in March</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27909 style="BORDER-TOP: medium none; WIDTH: 212pt; HEIGHT: 25.5pt" width=282 height=34 rowSpan=2> </TD><TD class=xl25909 style="BORDER-TOP: medium none; WIDTH: 180pt" width=240 rowSpan=2>Australia - Tasmania</TD><TD class=xl22909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt" width=350>Start: <FONT class=font5909>First Sunday in October</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt; HEIGHT: 12.75pt" width=350 height=17>End: <FONT class=font5909>Last Sunday in March</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24909 style="BORDER-TOP: medium none; WIDTH: 212pt; HEIGHT: 25.5pt" width=282 height=34 rowSpan=2> </TD><TD class=xl24909 style="BORDER-TOP: medium none; WIDTH: 180pt" width=240 rowSpan=2>Fiji</TD><TD class=xl23909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt" width=350>Start: <FONT class=font5909>First Sunday in November</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt; HEIGHT: 12.75pt" width=350 height=17>End: <FONT class=font5909>Last Sunday in February</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25909 style="BORDER-TOP: medium none; WIDTH: 212pt; HEIGHT: 25.5pt" width=282 height=34 rowSpan=2> </TD><TD class=xl25909 style="BORDER-TOP: medium none; WIDTH: 180pt" width=240 rowSpan=2>New Zealand, Chatham</TD><TD class=xl22909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt" width=350>Start: <FONT class=font5909>First Sunday in October</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt; HEIGHT: 12.75pt" width=350 height=17>End: <FONT class=font5909>First Sunday on or after 5 March</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24909 style="BORDER-TOP: medium none; WIDTH: 212pt; HEIGHT: 25.5pt" width=282 height=34 rowSpan=2> </TD><TD class=xl24909 style="BORDER-TOP: medium none; WIDTH: 180pt" width=240 rowSpan=2>Tonga</TD><TD class=xl23909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt" width=350>Start: <FONT class=font5909>First Saturday in October</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt; HEIGHT: 12.75pt" width=350 height=17>End: <FONT class=font5909>First Saturday on or after 15 April</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22909 style="BORDER-TOP: medium none; WIDTH: 212pt; HEIGHT: 25.5pt" width=282 height=34 rowSpan=2>Europe</TD><TD class=xl25909 style="BORDER-TOP: medium none; WIDTH: 180pt" width=240 rowSpan=2>European Union, Russia</TD><TD class=xl22909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt" width=350>Start: <FONT class=font5909>Last Sunday in March</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt; HEIGHT: 12.75pt" width=350 height=17>End: <FONT class=font5909>Last Sunday in October</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24909 style="BORDER-TOP: medium none; WIDTH: 212pt; HEIGHT: 25.5pt" width=282 height=34 rowSpan=2> </TD><TD class=xl24909 style="BORDER-TOP: medium none; WIDTH: 180pt" width=240 rowSpan=2>Greenland</TD><TD class=xl23909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt" width=350>Start: <FONT class=font5909>First Sunday in April</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt; HEIGHT: 12.75pt" width=350 height=17>End: <FONT class=font5909>Last Sunday in October</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22909 style="BORDER-TOP: medium none; WIDTH: 212pt; HEIGHT: 25.5pt" width=282 height=34 rowSpan=2>North America</TD><TD class=xl25909 style="BORDER-TOP: medium none; WIDTH: 180pt" width=240 rowSpan=2>Canada, United States, Mexico, St. Johns, Bahamas, Turks and Caicos</TD><TD class=xl22909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt" width=350>Start: <FONT class=font5909>First Sunday in April</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt; HEIGHT: 12.75pt" width=350 height=17>End: <FONT class=font5909>Last Sunday in October</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24909 style="BORDER-TOP: medium none; WIDTH: 212pt; HEIGHT: 25.5pt" width=282 height=34 rowSpan=2> </TD><TD class=xl24909 style="BORDER-TOP: medium none; WIDTH: 180pt" width=240 rowSpan=2>Cuba</TD><TD class=xl23909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt" width=350>Start: <FONT class=font5909>April 1</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt; HEIGHT: 12.75pt" width=350 height=17>End: <FONT class=font5909>Last Sunday in October</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22909 style="BORDER-TOP: medium none; WIDTH: 212pt; HEIGHT: 25.5pt" width=282 height=34 rowSpan=2>South America</TD><TD class=xl25909 style="BORDER-TOP: medium none; WIDTH: 180pt" width=240 rowSpan=2>Brazil</TD><TD class=xl22909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt" width=350>Start: <FONT class=font5909>First Sunday in October</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt; HEIGHT: 12.75pt" width=350 height=17>End: <FONT class=font5909>Last Sunday in February</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24909 style="BORDER-TOP: medium none; WIDTH: 212pt; HEIGHT: 25.5pt" width=282 height=34 rowSpan=2> </TD><TD class=xl24909 style="BORDER-TOP: medium none; WIDTH: 180pt" width=240 rowSpan=2>Chile</TD><TD class=xl23909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt" width=350>Start: <FONT class=font5909>First Sunday on or after 9 October</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt; HEIGHT: 12.75pt" width=350 height=17>End: <FONT class=font5909>First Sunday on or after 9 March</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25909 style="BORDER-TOP: medium none; WIDTH: 212pt; HEIGHT: 25.5pt" width=282 height=34 rowSpan=2> </TD><TD class=xl25909 style="BORDER-TOP: medium none; WIDTH: 180pt" width=240 rowSpan=2>Falklands</TD><TD class=xl22909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt" width=350>Start: <FONT class=font5909>First Sunday on or after 8 September</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt; HEIGHT: 12.75pt" width=350 height=17>End: <FONT class=font5909>First Sunday on or after 6 April</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24909 style="BORDER-TOP: medium none; WIDTH: 212pt; HEIGHT: 25.5pt" width=282 height=34 rowSpan=2> </TD><TD class=xl24909 style="BORDER-TOP: medium none; WIDTH: 180pt" width=240 rowSpan=2>Paraguay</TD><TD class=xl23909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt" width=350>Start: <FONT class=font5909>First Sunday in October</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt; HEIGHT: 12.75pt" width=350 height=17>End: <FONT class=font5909>Last Saturday in February</FONT></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22909 style="BORDER-TOP: medium none; WIDTH: 212pt; HEIGHT: 12.75pt" width=282 height=17>Antarctica</TD><TD class=xl25909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 180pt" width=240>Antarctica</TD><TD class=xl25909 style="BORDER-TOP: medium none; BORDER-LEFT: medium none; WIDTH: 263pt" width=350>(same as Chile)</TD></TR></TBODY></TABLE></DIV>
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
=--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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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