Daylight Savings Time Funcitions

M

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!
 
Sort by date Sort by votes

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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.....
 
Upvote 0
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>
 
Last edited:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Need small fix to previous UTC post
Replies
0
Views
566
jdellasala
J
D
  • Question Question
Can't get MailMerge to work through VBA
Replies
0
Views
286
DetroitJimBurke
D
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
A
  • Solved
Calendar issue
Replies
1
Views
2K
AndyEd
A
E
  • Question Question
Dynamic Task Schedule
Replies
4
Views
502
EvenS8245
E

Forum statistics

Threads
1,224,521
Messages
6,179,285
Members
452,902
Latest member
Knuddeluff

Share this page

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