Getting UTC offset for dates different then 'right now'

**JasperD** · 09-13-2018, 04:33 AM

Hi all,

I currently use the code below to get the UTC offset between my system time/date and the UTC time/date

Public Declare PtrSafe Sub GetSystemTime Lib "Kernel32" (ByRef lpSystemTime As U)
Public Type U
Ye As Integer: Mo As Integer:   WD As Integer:    Da As Integer:    Hr As Integer:    Mn As Integer:    Se As Integer:    Ms As Integer
End Type
Sub GetUTCOffset()
Dim nU As U, UD As Date
Call GetSystemTime(nU)
UD = nU.Ye & "-" & nU.Mo & "-" & nU.Da & " " & nU.Hr & ":" & nU.Mn & ":" & nU.Se
Debug.Print Format(Now - UD, "HH:NN")
End Sub

This works great - for "right now" and returns 2hrs in my timezone.
But due to daylight saving time being different across the world, if I take let's say the 2hr offset and apply it for something I plan in November, it'll be wrong (as it'll be 1hr by then).
Is there an _easy_ way to check the UTC offset for different dates?
Thanks!

**bakerman2** · 09-13-2018, 09:38 PM

Following code isn't mine and I don't rememeber where I got so I can't give proper credit but it seems to do what you're after.

' System time structure
Public 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

' Time zone information. Note that this one is defined wrong in API viewer.
Private 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 Long
End Type

Private Declare Function SystemTimeToTzSpecificLocalTime Lib "Kernel32" (lpTimeZoneInformation As TIME_ZONE_INFORMATION, lpUniversalTime As SYSTEMTIME, lpLocalTime As SYSTEMTIME) As Long
Private Declare Function GetTimeZoneInformation Lib "Kernel32" (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long

' Convert UTC time to local time for current time zone
Public Function UTCtoLocal(ByVal tDate As Date) As Date
    Dim tzi As TIME_ZONE_INFORMATION
    Dim stUTC As SYSTEMTIME
    Dim stLocal As SYSTEMTIME
    Dim lres As Long
    
    lres = GetTimeZoneInformation(tzi)
    stUTC.wYear = Year(tDate)
    stUTC.wMonth = Month(tDate)
    stUTC.wDay = Day(tDate)
    stUTC.wHour = Hour(tDate)
    stUTC.wMinute = Minute(tDate)
    stUTC.wSecond = Second(tDate)
    stUTC.wMilliseconds = 0
    lres = SystemTimeToTzSpecificLocalTime(tzi, stUTC, stLocal)
    UTCtoLocal = DateSerial(stLocal.wYear, stLocal.wMonth, stLocal.wDay) + TimeSerial(stLocal.wHour, stLocal.wMinute, stLocal.wSecond)
End Function