Get Timezone Information in VBA (Excel)

VBA doesn't offer functions to do that, but the Windows API does. Luckily you can use all those functionality from VBA as well. This page describes how to do it: Time Zones & Daylight Savings Time

Edit: Added Code

For the posterity sake, I've added the complete code from Guru Chip's page, as usable in 32-bit Office VBA. (64-bit modification here)

Option ExplicitOption Compare Text'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' modTimeZones' By Chip Pearson, used with permission from www.cpearson.com' Date: 2-April-2008' Page Specific URL: www.cpearson.com/Excel/TimeZoneAndDaylightTime.aspx'' This module contains functions related to time zones and GMT times.'   Terms:'   -------------------------'   GMT = Greenwich Mean Time. Many applications use the term'       UTC (Universal Coordinated Time). GMT and UTC are'       interchangable in meaning,'   Local Time = The local "wall clock" time of day, that time that'       you would set a clock to.'   DST = Daylight Savings Time'   Functions In This Module:'   -------------------------'       ConvertLocalToGMT'           Converts a local time to GMT. Optionally adjusts for DST.'       DaylightTime'           Returns a value indicating (1) DST is in effect, (2) DST is'           not in effect, or (3) Windows cannot determine whether DST is'           in effect.'       GetLocalTimeFromGMT'           Converts a GMT Time to a Local Time, optionally adjusting for DST.'       LocalOffsetFromGMT'           Returns the number of hours/minutes between the local time &GMT,'           optionally adjusting for DST.'       SystemTimeToVBTime'           Converts a SYSTEMTIME structure to a valid VB/VBA date.'       LocalOffsetFromGMT'           Returns the number of minutes or hours that are to be added to'           the local time to get GMT. Optionally adjusts for DST.'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Required TypesPrivate 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 IntegerEnd TypePrivate 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 LongEnd TypePublic Enum TIME_ZONE    TIME_ZONE_ID_INVALID = 0    TIME_ZONE_STANDARD = 1    TIME_ZONE_DAYLIGHT = 2End Enum' Required Windows API DeclaresPrivate Declare Function GetTimeZoneInformation Lib "kernel32" _    (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As LongPrivate Declare Sub GetSystemTime Lib "kernel32" _    (lpSystemTime As SYSTEMTIME)Function ConvertLocalToGMT(Optional LocalTime As Date, _    Optional AdjustForDST As Boolean = False) As Date'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ConvertLocalToGMT' This converts a local time to GMT. If LocalTime is present, that local' time is converted to GMT. If LocalTime is omitted, the current time is' converted from local to GMT. If AdjustForDST is Fasle, no adjustments' are made to accomodate DST. If AdjustForDST is True, and DST is' in effect, the time is adjusted for DST by adding'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''    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 = LocalTime    End If    DST = GetTimeZoneInformation(TZI)    If AdjustForDST = True Then        GMT = T + TimeSerial(0, TZI.Bias, 0) + _                IIf(DST=TIME_ZONE_DAYLIGHT,TimeSerial(0, TZI.DaylightBias,0),0)    Else        GMT = T + TimeSerial(0, TZI.Bias, 0)    End If    ConvertLocalToGMT = GMTEnd FunctionFunction GetLocalTimeFromGMT(Optional StartTime As Date) As Date'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' GetLocalTimeFromGMT' This returns the Local Time from a GMT time. If StartDate is present and' greater than 0, it is assumed to be the GMT from which we will calculate' Local Time. If StartTime is 0 or omitted, it is assumed to be the GMT' local 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 = StartTime    End If    DST = GetTimeZoneInformation(TZI)    LocalTime = GMT - TimeSerial(0, TZI.Bias, 0) + _            IIf(DST = TIME_ZONE_DAYLIGHT, TimeSerial(1, 0, 0), 0)    GetLocalTimeFromGMT = LocalTimeEnd FunctionFunction 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 WithEnd FunctionFunction LocalOffsetFromGMT(Optional AsHours As Boolean = False, _    Optional AdjustForDST As Boolean = False) As Long'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 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.)'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''    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 = TBiasEnd FunctionFunction DaylightTime() As TIME_ZONE'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' DaylightTime' 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)    DaylightTime = DSTEnd Function

Please be aware of little trap in the solution.

The GetTimeZoneInformation() call returns DST info about the current time, but the converted date might be from the period with the different DST setting - thus converting January date in August would apply the current Bias, thus yielding the GMT date 1 hour less than the correct one (SystemTimeToTzSpecificLocalTime seems to be a better fit - untested yet)

The same applies when the date is from another year - when DST rules might have been different. GetTimeZoneInformationForYear should handle changes in different years. I'll put a code sample here once completed.

It also seems Windows does not provide a reliable way to get 3 letter abbreviation of the timezone (Excel 2013 supports zzz in Format() - not tested).

Edit 16.04.2015: IntArrayToString() removed as it is already present in modWorksheetFunctions.bas referenced in below mentioned cpearson.com articles.

Adding code to convert using timezone active at the time of the converted date (this issue is not addressed on cpearson.com). Error handling is not included for brevity.

Private Type DYNAMIC_TIME_ZONE_INFORMATION_VB    Bias As Long    StandardName As String    StandardDate As Date    StandardBias As Long    DaylightName As String    DaylightDate As Date    DaylightBias As Long    TimeZoneKeyName As String    DynamicDaylightTimeDisabled As LongEnd TypePrivate Declare Function GetTimeZoneInformationForYear Lib "kernel32" ( _    wYear As Integer, _    lpDynamicTimeZoneInformation As DYNAMIC_TIME_ZONE_INFORMATION, _    lpTimeZoneInformation As TIME_ZONE_INFORMATION _) As LongPrivate Declare Function GetDynamicTimeZoneInformation Lib "kernel32" ( _    pTimeZoneInformation As DYNAMIC_TIME_ZONE_INFORMATION _) As LongPrivate Declare Function TzSpecificLocalTimeToSystemTimeEx Lib "kernel32" ( _    lpDynamicTimeZoneInformation As DYNAMIC_TIME_ZONE_INFORMATION, _    lpLocalTime As SYSTEMTIME, _    lpUniversalTime As SYSTEMTIME _) As LongFunction LocalSerialTimeToGmt(lpDateLocal As Date) As Date    Dim retval As Boolean, lpDateGmt As Date, lpSystemTimeLocal As SYSTEMTIME, lpSystemTimeGmt As SYSTEMTIME    Dim lpDTZI As DYNAMIC_TIME_ZONE_INFORMATION     retval = SerialTimeToSystemTime(lpDateLocal, lpSystemTimeLocal)    retval = GetDynamicTimeZoneInformation(lpDTZI)    retval = TzSpecificLocalTimeToSystemTimeEx(lpDTZI, lpSystemTimeLocal, lpSystemTimeGmt)    lpDateGmt = SystemTimeToSerialTime(lpSystemTimeGmt)    LocalSerialTimeToGmt = lpDateGmtEnd Function

There are 2 ways to achieve offset:

1. subtract local date and converted gmt date:

   offset = (lpDateLocal - lpDateGmt)*24*60

2. get TZI for specific year and calculate:

   dst = GetTimeZoneInformationForYear(Year(lpDateLocal), lpDTZI, lpTZI)offset = lpTZI.Bias + IIf(lpDateLocal >= SystemTimeToSerialTime(lpTZI.DaylightDate) And lpDateLocal < SystemTimeToSerialTime(lpTZI.StandardDate), lpTZI.DaylightBias, lpTZI.StandardBias)

Caveat: For some reason, values populated in lpTZI here do not contain the year information, so you need to set the year in lpTZI.DaylightDate and lpTZI.StandardDate.

Here is the code that is referenced in the answer by 0xA3. I had to change the declare statements to allow it run properly in Office 64bit but I haven't been able to test again in Office 32bit. For my use I was trying to create ISO 8601 dates with timezone information. So i used this function for that.

Public Function ConvertToIsoTime(myDate As Date, includeTimezone As Boolean) As String    If Not includeTimezone Then        ConvertToIsoTime = Format(myDate, "yyyy-mm-ddThh:mm:ss")    Else        Dim minOffsetLong As Long        Dim hourOffset As Integer        Dim minOffset As Integer        Dim formatStr As String        Dim hourOffsetStr As String        minOffsetLong = LocalOffsetFromGMT(False, True) * -1        hourOffset = minOffsetLong \ 60        minOffset = minOffsetLong Mod 60        If hourOffset >= 0 Then            hourOffsetStr = "+" + CStr(Format(hourOffset, "00"))        Else            hourOffsetStr = CStr(Format(hourOffset, "00"))        End If        formatStr = "yyyy-mm-ddThh:mm:ss" + hourOffsetStr + ":" + CStr(Format(minOffset, "00"))        ConvertToIsoTime = Format(myDate, formatStr)    End IfEnd Function

The code below came from http://www.cpearson.com/excel/TimeZoneAndDaylightTime.aspx

Option ExplicitOption Compare Text''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' modTimeZones' By Chip Pearson, chip@cpearson.com, www.cpearson.com' Date: 2-April-2008' Page Specific URL: www.cpearson.com/Excel/TimeZoneAndDaylightTime.aspx'' This module contains functions related to time zones and GMT times.'   Terms:'   -------------------------'   GMT = Greenwich Mean Time. Many applications use the term'       UTC (Universal Coordinated Time). GMT and UTC are'       interchangable in meaning,'   Local Time = The local "wall clock" time of day, that time that'       you would set a clock to.'   DST = Daylight Savings Time'   Functions In This Module:'   -------------------------'       ConvertLocalToGMT'           Converts a local time to GMT. Optionally adjusts for DST.'       DaylightTime'           Returns a value indicating (1) DST is in effect, (2) DST is'           not in effect, or (3) Windows cannot determine whether DST is'           in effect.'       GetLocalTimeFromGMT'           Converts a GMT Time to a Local Time, optionally adjusting for DST.'       LocalOffsetFromGMT'           Returns the number of hours or minutes between the local time and GMT,'           optionally adjusting for DST.'       SystemTimeToVBTime'           Converts a SYSTEMTIME structure to a valid VB/VBA date.'       LocalOffsetFromGMT'           Returns the number of minutes or hours that are to be added to'           the local time to get GMT. Optionally adjusts for DST.'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Required Types'''''''''''''''''''''''''''''''''''''''''''''''''''''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 IntegerEnd TypePrivate 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 LongEnd TypePublic Enum TIME_ZONE    TIME_ZONE_ID_INVALID = 0    TIME_ZONE_STANDARD = 1    TIME_ZONE_DAYLIGHT = 2End Enum'''''''''''''''''''''''''''''''''''''''''''''''''''''' Required Windows API Declares'''''''''''''''''''''''''''''''''''''''''''''''''''''#If VBA7 Then    Private Declare PtrSafe Function GetTimeZoneInformation Lib "kernel32" _    (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long#Else    Private Declare Function GetTimeZoneInformation Lib "kernel32" _    (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long#End If#If VBA7 Then    Private Declare PtrSafe Sub GetSystemTime Lib "kernel32" _        (lpSystemTime As SYSTEMTIME)#Else    Private Declare Sub GetSystemTime Lib "kernel32" _        (lpSystemTime As SYSTEMTIME)#End IfFunction ConvertLocalToGMT(Optional LocalTime As Date, _    Optional AdjustForDST As Boolean = False) As Date'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ConvertLocalToGMT' This converts a local time to GMT. If LocalTime is present, that local' time is converted to GMT. If LocalTime is omitted, the current time is' converted from local to GMT. If AdjustForDST is Fasle, no adjustments' are made to accomodate DST. If AdjustForDST is True, and DST is' in effect, the time is adjusted for DST by adding'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Dim T As DateDim TZI As TIME_ZONE_INFORMATIONDim DST As TIME_ZONEDim GMT As DateIf LocalTime <= 0 Then    T = NowElse    T = LocalTimeEnd IfDST = GetTimeZoneInformation(TZI)If AdjustForDST = True Then    GMT = T + TimeSerial(0, TZI.Bias, 0) + _            IIf(DST = TIME_ZONE_DAYLIGHT, TimeSerial(0, TZI.DaylightBias, 0), 0)Else    GMT = T + TimeSerial(0, TZI.Bias, 0)End IfConvertLocalToGMT = GMTEnd FunctionFunction GetLocalTimeFromGMT(Optional StartTime As Date) As Date'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' GetLocalTimeFromGMT' This returns the Local Time from a GMT time. If StartDate is present and' greater than 0, it is assumed to be the GMT from which we will calculate' Local Time. If StartTime is 0 or omitted, it is assumed to be the GMT' local time.'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Dim GMT As DateDim TZI As TIME_ZONE_INFORMATIONDim DST As TIME_ZONEDim LocalTime As DateIf StartTime <= 0 Then    GMT = NowElse    GMT = StartTimeEnd IfDST = GetTimeZoneInformation(TZI)LocalTime = GMT - TimeSerial(0, TZI.Bias, 0) + _        IIf(DST = TIME_ZONE_DAYLIGHT, TimeSerial(1, 0, 0), 0)GetLocalTimeFromGMT = LocalTimeEnd FunctionFunction 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 WithEnd FunctionFunction LocalOffsetFromGMT(Optional AsHours As Boolean = False, _    Optional AdjustForDST As Boolean = False) As Long'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 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.)'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Dim TBias As LongDim TZI As TIME_ZONE_INFORMATIONDim DST As TIME_ZONEDST = GetTimeZoneInformation(TZI)If DST = TIME_ZONE_DAYLIGHT Then    If AdjustForDST = True Then        TBias = TZI.Bias + TZI.DaylightBias    Else        TBias = TZI.Bias    End IfElse    TBias = TZI.BiasEnd IfIf AsHours = True Then    TBias = TBias / 60End IfLocalOffsetFromGMT = TBiasEnd FunctionFunction DaylightTime() As TIME_ZONE'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' DaylightTime' 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_INFORMATIONDim DST As TIME_ZONEDST = GetTimeZoneInformation(TZI)DaylightTime = DSTEnd Function