2

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?

Erik A
  • 31,639
  • 12
  • 42
  • 67
ashleedawg
  • 20,365
  • 9
  • 72
  • 105
  • If you're only determining the *current* offset, then logically you can only use it to convert the *current* timestamp. You have no guarantee that the offset won't be different from one timestamp to the next. – Matt Johnson-Pint Jun 09 '18 at 23:39

1 Answers1

2

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
  • Example Usage: MsgBox hoursOffsetFromUTC

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
  • Example usage: MsgBox hoursOffsetFromUTC_Win

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:

  • To convert a numeric Unix/epoch timestamp to "Excel Time":

    ( Timestamp / 86400 ) + 25569 = ExcelTime

  • or, in reverse, from Excel to epoch timestamp:

    ( ExcelTime - 25569 ) * 86400 = Timestamp

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

More Information:

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
  • 1
    Is it worth using compiler defs to provide both 32 and 64 bitGetTimeZoneInformation imports? –  Jun 09 '18 at 08:17
  • @Jeeped Yes..... I'm fighting with it. It's tough for me to code & test because it runs fine for me with or without the 32/64 syntax – ashleedawg Jun 09 '18 at 10:16
  • Why are you generating a random number to pass for the timestamp in option 1? By doing so, you're not necessarily going to be working with the *current* offset. You should instead be passing the timestamp you plan to convert. – Matt Johnson-Pint Jun 09 '18 at 23:41