excelvbams-accesstimezonetimezone-offset

Retrieve current number of hours Local Machine is offset from UTC Time (with VBA)


People use my projects in multiple time zones.

What's the quickest way to retrieve only the current number of hours the local machine is offset from UTC using VBA, for the purpose of converting timestamps?


Solution

  • Here are two ready-to-use ways to retrieve the current number of hours offset from UTC time, nothing more, nothing less:

    Method One: Using 'any' API

    Option Explicit
    
    Function hoursOffsetFromUTC() As Single
        'returns current #hours difference between UTC & Local System Time
        'On Error GoTo uError
        Dim xmlHTTP As Object, strUTC As String, dtUTC As Date
        Set xmlHTTP = CreateObject("MSXML2.XMLHTTP")
        xmlHTTP.Open "GET", "https://maps.googleapis.com/maps/api/" & _
            "timezone/json?location=" & Int(Rnd() * 99) & ",0&timestamp=" & Int(Rnd() * 99), False
        xmlHTTP.send 'send randomized reqeust to avoid cached results
        strUTC = Mid(xmlHTTP.getResponseHeader("date"), 6, 20)
        Set xmlHTTP = Nothing
        dtUTC = DateValue(strUTC) + TimeValue(strUTC)
        hoursOffsetFromUTC = Round((Now() - dtUTC) * 48, 0) / 2 'nearest 0.5
        Exit Function
    uError:
        MsgBox "Couldn't get UTC time." & vbLf & vbLf & _
            "Err#" & Err & ": " & Err.Description, vbExclamation, "Error!"
    End Function
    

    Method Two: Using Windows API

    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 LongPtr
        StandardName(0 To 31) As Integer
        StandardDate As SYSTEMTIME
        StandardBias As LongPtr
        DaylightName(0 To 31) As Integer
        DaylightDate As SYSTEMTIME
        DaylightBias As LongPtr
    End Type
    
    Private Declare PtrSafe Function GetTimeZoneInformation Lib "kernel32" _
        (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long
    
    Function hoursOffsetFromUTC_Win() As Single
        Dim TZI As TIME_ZONE_INFORMATION
        If GetTimeZoneInformation(TZI) = 2 Then
            hoursOffsetFromUTC_Win = 0 - ((TZI.Bias + TZI.DaylightBias) / 60)
        Else
            hoursOffsetFromUTC_Win = 0 - (TZI.Bias / 60)
        End If
    End Function
    

    Method One is less code but requires an internet connection. It calls a Google API with a random number to avoid caching, and ignoring the response body, it grabs that request date returned in the response header and compares it to the local system time. (Any API that returns current UTC/GMT in a header can be used.)

    Method Two requires declaration of two types and an external function, but runs without an internet connection, using functionality from Windows' internal kernel32 API.


    Converting Timestamps:

    (These don't inlude the timezone adjustment, so you can add/subtract it as required.)


    More Information: