How do you determine Daylight Savings Time in VBA?

Question

What function will let us know whether a date in VBA is in DST or not?

Samala

Great question! Why downvotes?! – Commove

Answer 1

For non-current dates (DST 2007+):

First, you need a function to find the number of specific weekdays in a month:

Public Function NDow(Y As Integer, M As Integer, _
                N As Integer, DOW As Integer) As Date  

' Returns Date of Nth Day of the Week in Month  

NDow = DateSerial(Y, M, (8 - Weekday(DateSerial(Y, M, 1), _
              (DOW + 1) Mod 8)) + ((N - 1) * 7))  

End Function  

Then, you can check for the DST day versus the following function calls:

Fall: NDow(Year(newdate), 11, 1, 1)
Spring: NDow(Year(newdate), 3, 2, 1)

For the current date:

Call the Windows API function GetTimeZoneInformation, and it will return an enum (integer) with the status.

I got the code for this from Chip Pearson's great Excel site.

Pearson's site

Samala

Except that this differs per country. – Commove

But the date of changing from ST to DT also contains some hours of ST. And likewise for the date of changing from DT to ST contains some hours of DT. The changing between ST/DT does not occur at midnight (12:00 AM) when the date changes. So your method will report incorrectly during those times. – Everyplace

@NOYB, this tells you whether a specific DATE is a DST transition day or not. After you figure that out you have to figure out which hours matter. – Samala

Answer 2

For anyone wondering how to account for daylight saving time in Europe (central europe time), I modified the script from Chip Pearson. The last sunday of March (2 AM to 3 AM) and October (3 AM to 2 AM) are the days when the hour switching occurs.

Following code is the click event of a button in Excel:

Dim dates As String
dates = "A1:A20"

For Each c In Worksheets("Sheet1").Range(dates).Cells
    If (IsDateWithinDST(c.Value)) Then
        c.Value = DateAdd("h", 1, c.Value)
    End If
Next

The module containing the necessary methods are to be found here.

More info on DST in Europe.

Gymnastic

Answer 3

Here's how I check for DST in the US. Of course, there are nuances such as AZ not covered.
Many of the DST checks I've seen online only account for the date and ignore the time. Consequently, they incorrectly report between 1AM and 2AM as DST after DT has been reverted to ST.
This not only accounts for the time but should also account for the recurrence of 1 AM to 2 AM when changing back to standard time.

' Parameters:
' GMT as a date or timestamp (seconds since 1/1/1970 00:00:00 UTC)
' TZstos as a long (timezone standard time offset in seconds)
' Return array (boolean, gmtOffset)
Public Function IsDST(ByVal GMT As Variant, TZstos As Long) As Variant

    ' Convert a date to timestamp
    If IsDate(GMT) And Not IsNumeric(GMT) Then
        GMT = DateDiff("s", "1/1/1970", GMT)
    End If

    Dim DST_Start_By   As Date,    DST_End_By   As Date
    Dim DST_Start_Date As Date,    DST_End_Date As Date
    Dim DST_Start_TS   As Double,  DST_End_TS   As Double
    Dim TZ_Std_time    As Double,  TZ_DST_time  As Double

    ' Timezone standard and daylight time from GMT
    TZ_Std_time = GMT + TZstos
    TZ_DST_time = GMT + TZstos + 3600

    ' DST Starts at 2:00 AM on second Sunday in March
    DST_Start_By = Year(DateAdd("s", TZ_Std_time, "1/1/1970")) & "-03-14"
    DST_Start_Date = DST_Start_By - (Weekday(DST_Start_By, 1) - 1)
    DST_Start_TS = DateDiff("s", "1/1/1970", DST_Start_Date) + (2 * 3600)

    ' DST Ends at 2:00 AM on first Sunday in November
    DST_End_By = Year(DateAdd("s", TZ_Std_time, "1/1/1970")) & "-11-07"
    DST_End_Date = DST_End_By - (Weekday(DST_End_By, 1) - 1)
    DST_End_TS = DateDiff("s", "1/1/1970", DST_End_Date) + (2 * 3600)

    ' Is it DST and gmtOffset
    If TZ_Std_time >= DST_Start_TS And _
       TZ_DST_time <  DST_End_TS Then
        IsDST = Array(True,  CLng(TZstos + 3600))
    Else
        IsDST = Array(False, CLng(TZstos +    0))
    End If

End Function

Call IsDST like so...

' GMT offset EST or EDT (standard or daylight time)
TZstatus = IsDST(GMT, (-5 * 3600)) ' GMT and EST offset in seconds
DST = TZstatus(0)
gmtOffset = TZstatus(1)

Everyplace

Answer 4

I wrote the following simple VBA script to determine if the date is within UK daylight savings time. I know it is not the best code, but it works for me:

Public Function isDST_UK(Optional date_to_check As Date)

Dim last_sunday_in_march As Date
Dim last_sunday_in_october As Date
Dim temp_date As Date

' If no date entered use today's date
If date_to_check = "00:00:00" Then date_to_check = Now()
' Get last Sunday in March
temp_date = DateSerial(Year(Now), 3, 31)
Do Until Weekday(temp_date) = 1
    temp_date = temp_date - 1
Loop
last_sunday_in_march = temp_date
' Get last Sunday in October
temp_date = DateSerial(Year(Now), 10, 31)
Do Until Weekday(temp_date) = 1
    temp_date = temp_date - 1
Loop
last_sunday_in_october = temp_date
' Compare date_to_check against last Sunday in March and last Sunday in October
isDST_UK = (date_to_check >= last_sunday_in_march And date_to_check < last_sunday_in_october)

End Function

It does not take into account the time of day as I don't need it too (due to only using the code during the day), but that could be added easily enough if required.

Copybook