Time Zones In VBA 

All of the date and time functions in Excel and VBA use local time, that is, the current time of day in your location.  They do not take into account time zones or Daylight Savings Time.  For most purposes, this is adequate.  However, if your application needs to accommodate multiple time zones, you'll need to be able to determine the absolute time, or Greenwich Mean Time.  

Greenwich Mean Time (GMT), also called Universal Coordinated Time (UTC) or Zulu Time, is the time of day in Greenwich, England, which is at 0 degrees longitude.  GMT is not adjusted for Daylight Savings Time (DST) and all time zones around the world are computed as offsets from GMT.  For example, Central Standard Time in the United States is GMT -6, that is, 6 hours earlier than GMT. 

VBA doesn't give you any built-in functions for determining either GMT or your offset from it, or what time zone your are located in.  Therefore, you'll have to use two of the Windows API (or Application Programmatic Interface) calls, to get this information directly from Windows.   This page describes the techniques for doing this.  

The Windows API procedures, GetTimeZoneInformation and GetSystemTime are a bit tricky to use for novice programmers, so I've wrapped these procedures into a Class Module, so that your VBA code can simply read a few simple properties of this class without having to worry about the dirty details.  

You can download a workbook which contains all the code we'll be using.

The class module declares the following API functions and Types. 

Private Declare Function GetTimeZoneInformation Lib "kernel32" _
    (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long

Private Declare Sub GetSystemTime Lib "kernel32" _
    (lpSystemTime As SYSTEMTIME)

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

Private Type TIME_ZONE_INFORMATION
    Bias As Long
    StandardName(31) As Integer
    StandardDate As SYSTEMTIME
    StandardBias As Long
    DaylightName(31) As Integer
    DaylightDate As SYSTEMTIME
    DaylightBias As Long
End Type


Also, you'll want to include the following variables.  Excel 97 and earlier versions do not support the Enum data type. For Excel 97, see the code in the downloadable file.

Public Enum cstDSTType
    cstDSTUnknown = 0
    cstDSTStandard = 1
    cstDSTDaylight = 2
End Enum

Private SysTime As SYSTEMTIME
Private TZInfo As TIME_ZONE_INFORMATION
Private TZType As cstDSTType


These are how we'll access the time zone information from Windows.

NOTE: If you use the WinAPI Viewer add-in for VBA to copy these declaration into your code, the Type declaration for TIME_ZONE_INFORMATION is incorrect.  You must change the size of both StandardName and DaylightName from 32 to 31.  Otherwise, the procedure calls will not work. 

To get the current time zone information, we call GetTimeZoneInformation as follows.

TZType = GetTimeZoneInformation(TZInfo)

This will fill the TZInfo structure with the information we need, and return a value indicating whether we are currently in Daylight Savings Time.  There are three elements of the TZInfo structure we're interested in.

Bias
This is the number of minutes added to the local time to get GMT.  Therefore, if Bias is 360, this indicates that we are 6 hours (360 minutes) behind GMT.  

StandardName
This is the name of the standard time zone for your location.  

DaylightName
This is the name of the daylight savings time zone for your location.

Both StandardName and DaylightName are returned from Windows as an array of integers, not strings. Therefore, it is necessary to convert this array to a string.  My class module provides a procedure to do this, shown below. 

Private Function StringFromIntArray(IntArray() As Integer) As String

Dim Ndx As Long
Dim C As String
Do Until IntArray(Ndx) = 0
    C = C & Chr(IntArray(Ndx))
    Ndx = Ndx + 1
Loop
StringFromIntArray = C

End Function

As I said, all of this can get a little tricky.  However, if you use the CTime class provided in the workbook, you can avoid all this code yourself, and just call upon the properties of the class. For example, to return the current time zone (for your location, and taking into account daylight savings time), you would simple code the following.  

Dim TInfo As CTime
Set TInfo = New CTime
Debug.Print TInfo.TimeZoneName

All of the messy details are handled for you inside the class. When you create an instance of the class, it reads the current time.  To update the time used by the class, call its Refresh method. 

TInfo.Refresh

The GMT property will return the Greenwich Mean Time. For example,

Msgbox "GMT: " & Format(TInfo.GMT,"hh:mm:ss")

This will display the current GMT.  

You can also determine whether your are currently in Daylight Savings Time.

If TInfo.DSTInEffect = True Then
    MsgBox "We are in Daylight Time"
Else
    MsgBox "We are in Standard Time"
End If


The
TimeZoneOffset property returns the offset from GMT of the local time.  For example, if you are 6 hours behind GMT,  TimeZoneOffset will return -6.

 

 

        

Other Date And Time Related Procedures are described on the following pages.

        

Adding Months And Years

The DATEDIF Function

Date Intervals

Dates And Times

Date And Time Entry

Date And Time Arithmetic

Distributing Dates Over Intervals

Holidays

Julian Dates

Latitude And Longitude 

Overtime Hours And Timesheets


Time Zones In VBA

VBA Procedures For Dates And Times

Week Numbers

Worksheet Functions For Dates And Times

 

 

See the Dates And Times Topic Index For Information