Convert local time to UTC without calling Win APIs while taking into account DST

Question

Turns out my organisation have blocked all Win API calls from MS Office code with their ASR rules and are unwilling to apply any exclusions so any old code I have no longer works. Anyone help with Excel VBA code that can convert local datetime into UTC?GMT while taking into account DST. I tried using the COM object, but wasn't sure what to do with DST offsets. Users are in multiple countries and I'm not sure exactly where since most people work remotely.

Answer 1

Was directed to this answer that uses Outlook which gives exactly what I wanted. Noticeably slower than the Win API calls on large scale repetitive calls, but any complaints can be directed to the company's IT for their security policy change.

https://stackoverflow.com/a/45510712/16578424

Option Explicit

'mTimeZones by Patrick Honorez --- www.idevlop.com
'with the precious help of Julian Hess https://stackoverflow.com/a/45510712/78522
'You can reuse but please let all the original comments including this one.

'This modules uses late binding and therefore should not require an explicit reference to Outlook,
'however Outlook must be properly installed and configured on the machine using this module
'Module works with Excel and Access

Private oOutl As Object 'keep Outlook reference active, to save time in recurring calls
Private oOutlTimeZones As Object 'keep Outlook reference active, to save time in recurring calls
' seems to drop the reference if use previous scheme of returning boolean
' returning the actual object is more correct in any case
Private Function GetOutlookTimeZones() As Object
    If oOutl Is Nothing Or oOutlTimeZones Is Nothing Then
        Debug.Print "~"
        On Error Resume Next
        Err.Clear
        Set oOutl = GetObject(, "Outlook.Application")
        If Err.Number Then
            Err.Clear
            Set oOutl = CreateObject("Outlook.Application")
        End If
        Set oOutlTimeZones = oOutl.TimeZones
    End If
    Set GetOutlookTimeZones = oOutlTimeZones
    On Error GoTo 0
End Function

Function ConvertTime(DT As Date, Optional TZfrom As String = "Central Standard Time", _
                                 Optional TZto As String = "W. Europe Standard Time") As Date
'convert datetime with hour from Source time zone to Target time zone
'valid Source & Target time zones can be found in your registry under: HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Windows NT/CurrentVersion/Time Zones/
'this version using Outlook, properly handles Dailight Saving Times, including for past and future dates
'it includes a fix for the fact that ConvertTime seems to strip the seconds
'krammy85 2019-01-25 Edit: Outlook rounds minutes when it strips seconds, so modified code to strip seconds (without rounding) prior to running Outlook's ConvertTime.
    Dim sourceTZ As Object
    Dim destTZ As Object
    Dim seconds As Single
    Dim DT_SecondsStripped As Date
    Dim oOutlTimeZones As Object: Set oOutlTimeZones = GetOutlookTimeZones()
    If Not (oOutlTimeZones Is Nothing) Then
        'fix for ConvertTime stripping the seconds
        seconds = Second(DT) / 86400    'save the seconds as DateTime (86400 = 24*60*60)
        DT_SecondsStripped = DT - seconds
        Set sourceTZ = oOutlTimeZones.Item(TZfrom)
        Set destTZ = oOutlTimeZones.Item(TZto)
        ConvertTime = oOutlTimeZones.ConvertTime(DT_SecondsStripped, sourceTZ, destTZ) + seconds    'add the stripped seconds
    End If
End Function

' returns number of minutes ahead of UTC (positive number) or behind
Function GetOffsetAt(DT As Date, TZfrom As String) As Long
    Dim utc_DT As Date: utc_DT = ConvertTime(DT, TZfrom, "UTC")
    GetOffsetAt = DateDiff("n", utc_DT, DT)
End Function

Sub test_ConvertTime()
    Dim t As Date: t = #8/23/2017 6:15:05 AM#
    Debug.Print t, ConvertTime(t), Format(t - ConvertTime(t), "h")
    Debug.Print t, ConvertTime(t, "Central Standard Time", "W. Europe Standard Time"), Format(t - ConvertTime(t), "h")
End Sub

Sub test_DumpTZs()
    Dim TZ As Object: For Each TZ In GetOutlookTimeZones()
        Debug.Print "TZ:", TZ.Id, TZ.Name
    Next TZ
End Sub

Last two Subs are not really necessary.

For example, I can now simply refer to the following to get the user's current timezone, regardless of daylight saving, and return the UTC converted value. This could be wrapped in a Format function to display accordingly.

ConvertTime(Now, oOutlTimeZones.CurrentTimeZone.id, "UTC")