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_INFORMATIONstructure and the SYSTEMTIME structure are used by the API functions, they must be declared before theDeclare 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). IfAdjustForDST 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 SYSTEMTIMEstructure 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