Is it Standard Time yet?

From: Daily Does of Excel

Is it Standard Time yet?:

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.

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
©¿©¬