Time Zones And Daylight Savings Time

This page describes code for working with Time Zones and Daylight Savings Time.

Neither VBA nor VB6 provides any native functions for working with Time Zones, Greenwich Mean Time (GMT, also called UTC), or Daylight Savings Time. To work with these values, you must use some Windows Application Programmatic Interface (API) functions. This page describes these API functions and how to use them in your VBA code.

A NOTE OF CAUTION: Windows API functions are unforgiving. Any error, such as using the address of a variable rather than a value of the variable, may cause the entire Excel Application to crash immediately, and you will lose all of you unsaved work. Until you are compfortable with the various APIs, always save your work before running the code.

Names And Abbreviations: Although the name for the time of a official atomic clock is Universal Coordinated Time (UTC), we will use on this page and in the sample code, the term 'Greenwich Mean Time' or 'GMT'. 'GMT' and 'UTC' can be used interchangably. Daylight Savings Time is abbreviated 'DST' and Standard Time is abbreviated 'SST'.

You can download a bas module file containing all the code discussed on this page.

GetTimeZoneInformation

The core of all the functions presented on this pages is the API function GetTimeZoneInformation and the structure named TIME_ZONE_INFORMATION that the API function populates with data. You can learn much more about GetTimeZoneInformation and the TIME_ZONE_INFORMATION on MSDN at GetTimeZoneInformation.

Daylight Savings Time

The return value of the GetTimeZoneInformation API function indicates whether the current date and time are in Daylight Time, Standard Time, or if Windows is unable to determine whether the time is in Daylight Time or Standard Times. The code is shown below:

    Function DaylightMode() As TIME_ZONE
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' DaylightMode
    ' Returns a value indicating whether the current date is
    ' in Daylight Time, Standard Time, or that Windows cannot
    ' deterimine the time status. The result is a member or
    ' the TIME_ZONE enum.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim TZI As TIME_ZONE_INFORMATION
    Dim DST As TIME_ZONE
    DST = GetTimeZoneInformation(TZI)
    DaylightMode = DST
    End Function 

The code uses the following Enum declaration to indicate Daylight Times.

    Public Enum TIME_ZONE
        TIME_ZONE_ID_INVALID = 0    ' Cannot determine DST
        TIME_ZONE_STANDARD = 1      ' Standard Time, not Daylight
        TIME_ZONE_DAYLIGHT = 2      ' Daylight Time, not Standard
    End Enum

Time Zones

Time Zone information is retrieved with the API functions shown below. Since the TIME_ZONE_INFORMATION structure and the SYSTEMTIME structure are used by the API functions, they must be declared before the Declare Function declarations. You code module should look like the following:

    Option Explicit
    
    Private Type SYSTEMTIME
        wYear As Integer
        wMonth As Integer
        wDayOfWeek As Integer
        wDay As Integer
        wHour As Integer
        wMinute As Integer
        wSecond As Integer
        wMilliseconds As Integer
    End Type
    
    
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' NOTE: If you are using the Windows WinAPI Viewer Add-In to get
    ' function declarations, not that there is an error in the 
    ' TIME_ZONE_INFORMATION structure. It defines StandardName and
    ' DaylightName As 32. This is fine if you have an Option Base
    ' directive to set the lower bound of arrays to 1. However, if 
    ' your Option Base directive is set to 0 or you have no 
    ' Option Base diretive, the code won't work. Instead,
    ' change the (32) to (0 To 31).
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
    Private Type TIME_ZONE_INFORMATION
        Bias As Long
        StandardName(0 To 31) As Integer
        StandardDate As SYSTEMTIME
        StandardBias As Long
        DaylightName(0 TO 31) As Integer
        DaylightDate As SYSTEMTIME
        DaylightBias As Long
    End Type
    
    
    ''''''''''''''''''''''''''''''''''''''''''''''
    ' These give symbolic names to the time zone
    ' values returned by GetTimeZoneInformation .
    ''''''''''''''''''''''''''''''''''''''''''''''
    
    Private Enum TIME_ZONE
        TIME_ZONE_ID_INVALID = 0        ' Cannot determine DST
        TIME_ZONE_STANDARD = 1          ' Standard Time, not Daylight
        TIME_ZONE_DAYLIGHT = 2          ' Daylight Time, not Standard
    End Enum
    

    Private Declare Function GetTimeZoneInformation Lib "kernel32" _
        (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long
    
    Private Declare Sub GetSystemTime Lib "kernel32" _
        (lpSystemTime As SYSTEMTIME)

Once these declarations are in place, we can move forward to the real code.

Time Zone Information

Time zone information is returned by the GetTimeZoneInformation function. You pass the function an instance of the TIME_ZONE_INFORMATION structure and the function populates the fields of this structure. The return value of the function indicates whether the current time is within DST. The return value will be one of the values in the following Enum:

    Private Enum TIME_ZONE
        TIME_ZONE_ID_INVALID = 0        ' Cannot determine DST
        TIME_ZONE_STANDARD = 1          ' Standard Time, not Daylight
        TIME_ZONE_DAYLIGHT = 2          ' Daylight Time, not Standard
    End Enum

Using only the return value of GetTimeZoneInformation, you can determine whether the current date is within DST.

Sub TestDST()
    Dim TZI As TIME_ZONE_INFORMATION
    Dim DST As TIME_ZONE
    
    DST = GetTimeZoneInformation(TZI)
    Select Case DST
        Case TIME_ZONE_ID_INVALID
            Debug.Print "Windows cannot determine DST."
        Case TIME_ZONE_STANDARD
            Debug.Print "Current date is in Standard Time"
        Case TIME_ZONE_DAYLIGHT
            Debug.Print "Current date is in Daylight Time"
        Case Else
            Debug.Print "**** ERROR: Unknown Result From GetTimeZoneInformation"
    End Select
End Sub

You can turn this into a fuctions such as shown below:

    Public Function IsDST() As Boolean
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' IsDST
    ' Returns TRUE if the current data is within DST, FALSE
    ' if DST is not in effect or if Windows cannot determine
    ' DST setting.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Application.Volatile True
        Dim DST As TIME_ZONE
        Dim TZI As TIME_ZONE_INFORMATION
        DST = GetTimeZoneInformation(TZI)
        IsDST = (DST = TIME_ZONE_DAYLIGHT)
    End Function

The GetTimeZoneInformation function and the TIME_ZONE_INFORMATION structure elements provide much more information besides just the current time zone. First, it returns the name of the Standard Time Zone and the Daylight Time Zone. These values do not depend on whether the current time is within DST. The StandardName and DaylightName values are not String type variables. Instead, they are arrays of integers that can be translated to a string. The following function takes in an array of integers and returns a String based on those integer values in the array.

    Function IntArrayToString(V As Variant) As String
        Dim N As Long
        Dim S As String
        For N = LBound(V) To UBound(V)
            S = S & Chr(V(N))
        Next N
        IntArrayToString = S
    End Function

You can use this function like:

    Sub AAA()
        Dim TZI As TIME_ZONE_INFORMATION
        Dim DST As TIME_ZONE
        Dim StandardName As String
        
        DST = GetTimeZoneInformation(TZI)
        StandardName = IntArrayToString(TZI.StandardName)
        Debug.Print StandardName
    End Sub

This same method can be used with DaylightName.

The following fucntions convert Local Time to GMT and GMT to Local Time.

Convert Local Time To GMT

    Function ConvertLocalToGMT(Optional LocalTime As Date) As Date
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' ConvertLocalToGMT
    ' This function returns the GMT based on LocalTime, if provided.
    ' If LocalTime is not equal to 0, the GMT corresponding to LocalTime
    ' is returned. If LocalTime is 0, the GMT corresponding to the local
    ' time is returned. Since GMT isn't affected by DST, we need to
    ' subtract 1 hour if we are presently in GMT.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 
    Dim T As Date
    Dim TZI As TIME_ZONE_INFORMATION
    Dim DST As TIME_ZONE
    Dim GMT As Date
    
    If LocalTime <= 0 Then
        T = Now
    Else
        T = StartTime
    End If
    DST = GetTimeZoneInformation(TZI)
    GMT = T + TimeSerial(0, TZI.Bias, 0) - IIf(DST = TIME_ZONE_DAYLIGHT, TimeSerial(1, 0, 0), 0)
    ConvertLocalToGMT = GMT
    
    End Function

Convert GMT To Local Time

    Function GetLocalTimeFromGMT(Optional GMTTime As Date) As Date
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' GetLocalTimeFromGMT
    ' This returns the Local Time from a GMT time. If GMTTime is present and
    ' greater than 0, it is assumed to be the GMT from which we will calculate
    ' Local Time. If GMTTime is 0 or omitted, it is assumed to be the GMT
    ' time.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim GMT As Date
    Dim TZI As TIME_ZONE_INFORMATION
    Dim DST As TIME_ZONE
    Dim LocalTime As Date
    
    If StartTime <= 0 Then
        GMT = Now
    Else
        GMT = GMTTime
    End If
    DST = GetTimeZoneInformation(TZI)
    LocalTime = GMT - TimeSerial(0, TZI.Bias, 0) + IIf(DST = TIME_ZONE_DAYLIGHT, TimeSerial(1, 0, 0), 0)
    GetLocalTimeFromGMT = LocalTime
    
    End Function

Difference Between GMT And Local Time

The function below returns the number of hours or minutes that are to be added to the local time to get a GMT time. Normally, this can be done very simply with the Bias member of TIME_ZONE_INFORMATION structure. The function below does that but then adjusts for DST if the AdjustForDST parameter is True. If AdjustForDST is False or omitted, no DST conversion is performed. For example, the absolute difference between local time in Chicago is 6 hours behind London. If we are not in DST, the result is either 360 (if AsHours is False) or 6 (if AsHours is True). If we are presently in DST, and AdjustForDST is True, the result is 300 (if AsHours is True) or 5 (if AsHours is True). If AdjustForDST is True, the difference is the same as if we are not in DST. To convert in the opposite direction (GMT Offst To Local) simply multiply the result of this function.

Function LocalOffsetFromGMT(Optional AsHours As Boolean = False, _
    Optional AdjustForDST As Boolean = False) As Double
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' LocalOffsetFromGMT
' This returns the amount of time in minutes (if AsHours is omitted or
' false) or hours (if AsHours is True) that should be *added* to the
' local time to get GMT. If AdjustForDST is missing or false,
' the unmodified difference is returned. (e.g., Kansas City to London
' is 6 hours normally, 5 hours during DST. If AdjustForDST is False,
' the resultif 6 hours. If AdjustForDST is True, the result is 5 hours
' if DST is in effect.)
' Note that the return type of the function is a Double not a Long. This
' is to accomodate those few places in the world where the GMT offset 
' is not an even hour, such as Newfoundland, Canada, where the offset is
' on a half-hour displacement.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim TBias As Long
Dim TZI As TIME_ZONE_INFORMATION
Dim DST As TIME_ZONE
DST = GetTimeZoneInformation(TZI)

If DST = TIME_ZONE_DAYLIGHT Then
    If AdjustForDST = True Then
        TBias = TZI.Bias + TZI.DaylightBias
    Else
        TBias = TZI.Bias
    End If
Else
    TBias = TZI.Bias
End If
If AsHours = True Then
    TBias = TBias / 60
End If

LocalOffsetFromGMT = TBias

End Function

As a final note, the module contains a function named SystemTimeToVBTime which converts a SYSTEMTIME structure to a normal VB/VBA serial date and time. The code is shown below:

Function SystemTimeToVBTime(SysTime As SYSTEMTIME) As Date
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' SystemTimeToVBTime
' This converts a SYSTEMTIME structure to a VB/VBA date value.
' It assumes SysTime is valid -- no error checking is done.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
With SysTime
    SystemTimeToVBTime = DateSerial(.wYear, .wMonth, .wDay) + _
            TimeSerial(.wHour, .wMinute, .wSecond)
End With

End Function

You can download a bas module file containing all the code discussed on this page.

Last Updated: 6-May-2008