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?
Here are two ready-to-use ways to retrieve the current number of hours offset from UTC time, nothing more, nothing less:
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×tamp=" & 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
MsgBox hoursOffsetFromUTC
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
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.
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.)
Wikipedia : Unix Time & Digital Timestamps
EpochConverter : Epoch & Unix Timestamp Conversion Tools
Chip Pearson : Time Zones And Daylight Savings Time (Windows API)
Stack Overflow : Convert between arbitrary timezones (Zone→Zone)
Microsoft Support : How to use times and dates in Excel