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!

 

[ymcj01]

no responses ever - lame.

 

[egerds]

Necro response (my first post)
If you don't mind using a module try looking @ http://www.mvps.org/access/api/api0024.htm for example.

you would want to replace the 3 instances of Eval with evaluate

Here is a function I wrote using the above to branch which date time to convert a GMT time in a sql statement in a query table

Public Function Today_is_DLS(Optional dteDateTime As Date) As Boolean
If SummerTime() <= dteDateTime <= StandardTime() Then
Today_is_DLS = True
Else
Today_is_DLS = False
End If
End Function

I found the converted access module to be a lot more flexible then trying to calculate DLS with all the recent updates

 

[BigTom]

I stumbled across this thread by accident and the more I thought about it the more I felt had to post.

Looking through the posts/solutions, I really think the suggestions here are over thinking, and over doing, a fairly simple process.

Simply speaking, the request is to find out what the 4th Sunday of October and April (or any month for that matter).
The answer to that question is simply a matter of what day of the week the month starts on and
is most probably why there are inaccuracies in these calculated solutions.

This table illustrates what I mean:
If the 1st in on: Sun Mon Tue Wed Thu Fri Sat
Then the 4th Sunday is: 22 28 27 26 25 24 23

That being said, a simple CHOOSE from the WEEKDAY of the 1st of the month can accurately build the date of the 4th Sunday.

For example:
=DATEVALUE(NUMBERVALUE(TEXT(A2,"mm"))&"/"&CHOOSE(WEEKDAY(DATE(YEAR(A2),MONTH(A2),1),1),22,28,27,26,25,24,23)&"/"&NUMBERVALUE(TEXT(A2,"yyyy")))

To continue, now that the Sunday date is defined you simply compare, assuming the date in question is in A2 and the above formula is in C2:
=IF(A2>=C2,"Yes","No")

Now, this is an over simplification because you would also have to check to see if the month fell between April and October.
This might look something like:
=IF(AND((MONTH(A2)>=4),(MONTH(A2)<=10),(A2>=C2)),"Yes","No")

To add a 2AM time check, simply add that time to the end of the formula:
=(DATEVALUE(NUMBERVALUE(TEXT(A2,"mm"))&"/"&CHOOSE(WEEKDAY(DATE(YEAR(A2),MONTH(A2),1),1),22,28,27,26,25,24,23)&"/"&NUMBERVALUE(TEXT(A2,"yyyy")))+TIMEVALUE("02:00:00"))

The same comparator formula will then determine the point at which the DST will kick in.
Taking this one step further, and sort of addressing Bob's question, you could then determine the start and end of the DST range,
assuming the date (and time) in question is in column J:
April start: =(DATEVALUE(4&"/"&CHOOSE(WEEKDAY(DATE(YEAR(J3),MONTH(4),1),1),22,28,27,26,25,24,23)&"/"&NUMBERVALUE(TEXT(J3,"yyyy")))+TIMEVALUE("02:00:00"))
Oct end: =(DATEVALUE(10&"/"&CHOOSE(WEEKDAY(DATE(YEAR(J3),MONTH(10),1),1),22,28,27,26,25,24,23)&"/"&NUMBERVALUE(TEXT(J3,"yyyy")))+TIMEVALUE("02:00:00"))

And the comparator formula might be something like:
=IF(AND(J3>=K3,J3<=L3),"Yes","No")

Of course this could all be strung into a single formula, but as the group is discussing multiple iterations and needs for determining DST, I will leave that to the individual.

Good luck!
Tom

 

[Theerkadharshini]

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 of Australia in VBA. I want a code to change Indian date/ time to Australian date/time based on daylight savings of sydney Thanks

 

[BigTom]

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 of Australia in VBA. I want a code to change Indian date/ time to Australian date/time based on daylight savings of sydney Thanks

I am sorry I missed your response/question and just stumbled on it now.

Because you are factoring in another time zone, the most accurate way to do this is to take your starting point (IST, as I understand your question) and convert it back to UTC time by subtracting 5 hours and 30 minutes. Then, based on the date in question either being after the first Sunday in April (at 3 AM) or before the First Sunday in October (at 2 AM), i.e. AEDT, take the UTC time and add 11 hours. If not, then add 10 hours to the UTC time.

This should work, and be accurate, either using formulas or VBA.