Part of my part-time job schedules world-wide PC-chat conferences weekly, and I announce the time referenced to the East Coast. As daylight savings time is about to end, I wanted an algorithm that knew whether standard time or daylight savings time was in effect.
As I’m not worried about the 2:00AM change over, I can do it based on the date. Since 2007, if the month is December through February, standard time is in effect. If the month is April through October, daylight savings time is in effect. Daylight savings time starts the second Sunday in March, and ends the first Sunday in November.
So, by counting Sundays in March and November, I can toggle the time zone. This is what I came up with.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
Function TZ(SomeDay As String) As String Dim TestDay As Long, TestMonth As Long, TestYear As Long Dim IsDST As Boolean Dim i As Long, SundayCount As Long TestDay = VBA.Day(SomeDay) TestMonth = VBA.Month(SomeDay) TestYear = VBA.Year(SomeDay) IsDST = False 'months 1, 2, 12 Select Case TestMonth Case 3 For i = 1 To TestDay If VBA.Weekday(VBA.DateSerial(TestYear, 3, i)) = 1 Then SundayCount = SundayCount + 1 If SundayCount = 2 Then IsDST = True Exit For End If Next i Case 4, 5, 6, 7, 8, 9, 10 IsDST = True Case 11 For i = 1 To TestDay If VBA.Weekday(VBA.DateSerial(TestYear, 11, i)) = 1 Then SundayCount = SundayCount + 1 If SundayCount = 1 Then Exit For Next i If SundayCount = 0 Then IsDST = True End Select If IsDST Then TZ = “EDT” Else TZ = “EST” End Function |
The default WEEKDAY() function returns 1 for Sunday, so if there are 2 Sundays in March including the day in question, turn IsDST to TRUE, and stop the loop. Conversely, if there is 1 Sunday in November including the day in question, leave IsDST as FALSE, and stop the loop.
I use the VBA. leader to ensure this function works on a Mac. Needless to say, it also works in MSWord.
…mrt
©¿©¬
Posting code? Use <pre> tags for VBA and <code> tags for inline.