Daylight Savings Time Funcitions

[mainstreet14534]

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!

 

[bob6pp]

Absolutely Brilliant!!.
But how on earth does it work?

 

[barry houdini]

This formula will give you the last Sunday in March 2010

=DATE(2010,4,1)-WEEKDAY(DATE(2010,4,7))

The UK BST formula extends that form to this

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

......to give you an "array" of 3 dates, last Sunday of the previous year, last Sunday of March and last Sunday of October.

If A2 is in 2010, for instance, then that would give you this array

{40174,40251,40489}

where those numbers represent the dates Sun 27th Dec 2009, Sun 28th March 2010 and Sun 31st October 2010, the MATCH part then looks like this

=MATCH(A2,{40174,40251,40489})=2

A2 must be greater than the first date, so MATCH can't return an error, only either 1 (if A2 is >= 40174 and < 40251), 2 (if A2 >= 40251 and < 40489) or 3 (if A2 >= 40489). So if a 2 is returned the date must be between the last Sunday in March and the last Sunday in October, hence date is in BST period.....

 

[pezboy]

I was faced with a similar problem (return TRUE if date/time was in U.S. DST range). This seems to work (A2 is the cell with the date to check):
<code><date(year(a2),11,7)-weekday(date(year(a2),11,7),1)+1+2 24)=""><date(year(a2),11,7)-weekday(date(year(a2),11,7),1)+1+2 24)="">
=AND(A2>DATE(YEAR(A2),3,7)-WEEKDAY(DATE(YEAR(A2),3,7),1)+8+2/24,A2<DATE(YEAR(A2),11,7)-WEEKDAY(DATE(YEAR(A2),11,7),1)+1+2/24)
</date(year(a2),11,7)-weekday(date(year(a2),11,7),1)+1+2></date(year(a2),11,7)-weekday(date(year(a2),11,7),1)+1+2></code><date(year(a2),11,7)-weekday(date(year(a2),11,7),1)+1+2 24)=""></date(year(a2),11,7)-weekday(date(year(a2),11,7),1)+1+2>

 

[West Man]

How can Barry's formula be modified to include the 2 AM time of change?

 

[barry houdini]

Just like this.....

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

 

[West Man]

Thank you Barry. It should have been obvious, just add 2 hours.

 

[ymcj01]

Hi Barry,

Would you be able to show how this function can be applied to GMT dates? Ultimately my aim is to convert GMT dates to PPT dates.

So for example, if the function could return false for GMT dates 11/1/2009 9:00:00 AM to 3/14/2010 9:00:00 AM, then I could adjust the GMT dates by an offset of -8 hours to get PPT, and conversely when it's true, I would adjust the GMT dates by -7 hours.
Thanks,

J

 

[ymcj01]

Okay, would anyone be able to answer my question??

Again, I would like the function to return False for any GMT dates that are non-DST. So for example, it would return false for the following dates and hours ending:
11/1/2009 9:00:00 AM to 3/14/2010 9:00:00 AM


Or otherwise, if someone has a solution for how to convert GMT to PPT (pacific 'prevailing' time), accounting for DST changes, that would be very helpful.

Thanks!

J

 

[ymcj01]

wow, thanks for the help everyone. so helpful

 

[ymcj01]

Seriously, doesn't anyone have an answer?!