Convert UTC time to local

Question

I have an MS Access app which is used in two different time zones. The difference is 7 hours. I needed to find a time when the both of the offices are off so I can close their database and I can do compact and repair and do backup of them.

So I will not need to create two separates front end and I tell close database one at 1000 PM and another in 4 AM I figure out I can say Close database at 00:30 AM UTC. But I do not know how to convert the same one in Local. Right now my code for closing the database looks like this:

Private Sub Form_Timer()
Dim RunAtLocalTime As String

RunAtLocalTime = Format(Now(), "HH:MM:SS")
If RunAtLocalTime = ("00:00:00") Then
        DoCmd.Quit
End If
End Sub

And I want to do something like this:

Private Sub Form_Timer()
Dim RunAtLocalTime As String
Dim UTCTIME As 

'''RunAtLocalTime = Convert(UTCTIME)
 RunAtLocalTime = Format(Now(), "HH:MM:SS")
 If RunAtLocalTime = ("00:00:00") Then
        DoCmd.Quit
End If
End Sub

Michaeline

I think the following site explains what you are looking for quite well (for excel but the same applies to Access): cpearson.com/excel/LocalAndGMTTimes.htm – Thermopylae

Answer 1

I have found the following functions:

This one returns the UTC timezone offset:

Option Compare Database
Option Explicit

Private Declare Function GetTimeZoneInformation Lib "kernel32" (lpTimeZoneInformation As TimeZoneInfo) As Long


Private Type SystemTime
        intYear As Integer
        intMonth As Integer
        intwDayOfWeek As Integer
        intDay As Integer
        intHour As Integer
        intMinute As Integer
        intSecond As Integer
        intMilliseconds As Integer
End Type


Private Type TimeZoneInfo
        lngBias As Long
        intStandardName(32) As Integer
        intStandardDate As SystemTime
        intStandardBias As Long
        intDaylightName(32) As Integer
        intDaylightDate As SystemTime
        intDaylightBias As Long
End Type

Public Function GetUTCOffset() As Date
    Dim lngRet As Long
    Dim udtTZI As TimeZoneInfo

    lngRet = GetTimeZoneInformation(udtTZI)
    GetUTCOffset = udtTZI.lngBias / 60 / 24
End Function

source: [this site] (edit link no longer valid, removed)

And this one that converts a time to GMT:

Option Explicit
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 Long
    StandardName(31) As Integer
    StandardDate As SYSTEMTIME
    StandardBias As Long
    DaylightName(31) As Integer
    DaylightDate As SYSTEMTIME
    DaylightBias As Long
End Type

Private Declare Function GetTimeZoneInformation Lib "kernel32" (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long
'Purpose     :  Converts local time to GMT.
'Inputs      :  dtLocalDate                 The local data time to return as GMT.
'Outputs     :  Returns the local time in GMT.
'Author      :  Andrew Baker
'Date        :  13/11/2002 10:16
'Notes       :
'Revisions   :

Public Function ConvertLocalToGMT(dtLocalDate As Date) As Date
    Dim lSecsDiff As Long

    'Get the GMT time diff
    lSecsDiff = GetLocalToGMTDifference()
    'Return the time in GMT
    ConvertLocalToGMT = DateAdd("s", -lSecsDiff, dtLocalDate)
End Function

'Purpose     :  Converts GMT time to local time.
'Inputs      :  dtLocalDate                 The GMT data time to return as local time.
'Outputs     :  Returns GMT as local time.
'Author      :  Andrew Baker
'Date        :  13/11/2002 10:16
'Notes       :
'Revisions   :

Public Function ConvertGMTToLocal(gmtTime As Date) As Date
    Dim Differerence As Long

    Differerence = GetLocalToGMTDifference()
    ConvertGMTToLocal = DateAdd("s", Differerence, gmtTime)
End Function

'Purpose     :  Returns the time lDiff between local and GMT (secs).
'Inputs      :  dtLocalDate                 The local data time to return as GMT.
'Outputs     :  Returns the local time in GMT.
'Author      :  Andrew Baker
'Date        :  13/11/2002 10:16
'Notes       :  A positive number indicates your ahead of GMT.
'Revisions   :

Public Function GetLocalToGMTDifference() As Long
    Const TIME_ZONE_ID_INVALID& = &HFFFFFFFF
    Const TIME_ZONE_ID_STANDARD& = 1
    Const TIME_ZONE_ID_UNKNOWN& = 0
    Const TIME_ZONE_ID_DAYLIGHT& = 2

    Dim tTimeZoneInf As TIME_ZONE_INFORMATION
    Dim lRet As Long
    Dim lDiff As Long

    'Get time zone info
    lRet = GetTimeZoneInformation(tTimeZoneInf)

    'Convert diff to secs
    lDiff = -tTimeZoneInf.Bias * 60
    GetLocalToGMTDifference = lDiff

    'Check if we are in daylight saving time.
    If lRet = TIME_ZONE_ID_DAYLIGHT& Then
        'In daylight savings, apply the bias
        If tTimeZoneInf.DaylightDate.wMonth <> 0 Then
            'if tTimeZoneInf.DaylightDate.wMonth = 0 then the daylight
            'saving time change doesn't occur
            GetLocalToGMTDifference = lDiff - tTimeZoneInf.DaylightBias * 60
        End If
    End If
End Function

source : this site

The second one I believe uses the first one as well.

Either will suit your needs I believe.

Extramarital

Does this adjust for daylight savings time? – Kersten

Yes. Check msdn.microsoft.com/en-us/library/windows/desktop/… , GetTimeZoneInformation returns different values based on whether the timezone is affected by DST or not. – Extramarital

@Extramarital This function does NOT correctly convert historical times. This is a common mistake. This applies the current DST status to all dates (instead of adjusting based on the date being converted). Example: Convert #2018-06-06# to UTC, then Convert #2018-01-01# to UTC. With this method they will incorrectly have the same offsets. (Offset = Local time minus UTC time). Use timeanddate.com to check the result. Further info in my answer. – Kersten

The microsoft.com link you included clearly states that different functions are needed to support DST changes. Also, note that your linked source (dbforums) no longer exists and redirects to a (rather spammy) site. There's a tonne of misinformation circulating about correctly converting datetimes from the past/future, partly since the rules are constantly changing. (Another example issue is that many don't account for the 2007 DST rule change in USA & Canada) I've had a lot of frustration before finally finding a reliable method – Kersten

Answer 2

Be wary of which method you use for time zone conversion, including to/from the UTC standard. The rules for time zones (including differences in daylight savings) are confusing to start with, since they vary not only by region or country, but in some cases by state or county.

To further confuse matters, the rules are constantly evolving, so for logical reasons (like the remaining half the planet, hopefully, moving towards eliminating Daylight Savings), and sometimes not so logical (country leaders changing rules on a whim), and other times improperly communicated (case study: Turkey's 2015 Chaos).

Even Canada/USA had a major change in 2007 that coders are often forgetting to account for. Other solutions on this site (or this page!) miscalculate is certain situations or timeframes.

Ideally, we'd all get out information from the same place, using the same methods. The authority on future and historical time zone information is considered to be the tz database and related code published by iana.org

---

This following conversion method accounts for all Daylight Savings and Time Zone differences, which I diligently confirmed with lengthy analysis and through authoritative documents such as Unicode's Common Locale Data Repository.

I minimized for space and efficiency and included only functions relevant to my purposes: conversion between UTC time and local time, and between Epoch^† Timestamp and local time. This is an adaptation of code from Tim Hall.
^†_{Epoch Timestamp, also known an Unix time is the number of seconds since January 1, 1970, and is used as a standard time format in many API's and other programming resources. More info at epochconverter.com, and Wikipedia.}

I'd suggest that this be placed in a module by itself.

Option Explicit
'UTC/Local Time Conversion
'Adapted from code by Tim Hall published at https://github.com/VBA-tools/VBA-UtcConverter

'PUBLIC FUNCTIONS:
'    - UTCtoLocal(utc_UtcDate As Date) As Date     converts UTC datetimes to local
'    - LocalToUTC(utc_LocalDate As Date) As Date   converts local DateTime to UTC
'    - TimestampToLocal(st As String) As Date      converts epoch timestamp to Local Time
'    - LocalToTimestamp(dt as date) as String      converts Local Time to timestamp
'Accuracy confirmed for several variations of time zones & DST rules. (ashleedawg)
'===============================================================================

Private Type utc_SYSTEMTIME
    utc_wYear As Integer: utc_wMonth As Integer: utc_wDayOfWeek As Integer: utc_wDay As Integer
    utc_wHour As Integer: utc_wMinute As Integer: utc_wSecond As Integer: utc_wMilliseconds As Integer
End Type

Private Type utc_TIME_ZONE_INFORMATION
    utc_Bias As Long: utc_StandardName(0 To 31) As Integer: utc_StandardDate As utc_SYSTEMTIME: utc_StandardBias As Long
    utc_DaylightName(0 To 31) As Integer: utc_DaylightDate As utc_SYSTEMTIME: utc_DaylightBias As Long
End Type

'http://msdn.microsoft.com/library/windows/desktop/ms724421.aspx /ms724949.aspx /ms725485.aspx
Private Declare PtrSafe Function utc_GetTimeZoneInformation Lib "kernel32" Alias "GetTimeZoneInformation" _
    (utc_lpTimeZoneInformation As utc_TIME_ZONE_INFORMATION) As Long
Private Declare PtrSafe Function utc_SystemTimeToTzSpecificLocalTime Lib "kernel32" Alias "SystemTimeToTzSpecificLocalTime" _
    (utc_lpTimeZoneInformation As utc_TIME_ZONE_INFORMATION, utc_lpUniversalTime As utc_SYSTEMTIME, utc_lpLocalTime As utc_SYSTEMTIME) As Long
Private Declare PtrSafe Function utc_TzSpecificLocalTimeToSystemTime Lib "kernel32" Alias "TzSpecificLocalTimeToSystemTime" _
    (utc_lpTimeZoneInformation As utc_TIME_ZONE_INFORMATION, utc_lpLocalTime As utc_SYSTEMTIME, utc_lpUniversalTime As utc_SYSTEMTIME) As Long

Private Function utc_DateToSystemTime(utc_Value As Date) As utc_SYSTEMTIME ' "Helper Function" for Public subs (below)
    With utc_DateToSystemTime
        .utc_wYear = Year(utc_Value): .utc_wMonth = Month(utc_Value): .utc_wDay = Day(utc_Value)
        .utc_wHour = Hour(utc_Value): .utc_wMinute = Minute(utc_Value): .utc_wSecond = Second(utc_Value): .utc_wMilliseconds = 0
    End With
End Function

Private Function utc_SystemTimeToDate(utc_Value As utc_SYSTEMTIME) As Date ' "Helper Function" for Public Functions (below)
    utc_SystemTimeToDate = DateSerial(utc_Value.utc_wYear, utc_Value.utc_wMonth, utc_Value.utc_wDay) + _
        TimeSerial(utc_Value.utc_wHour, utc_Value.utc_wMinute, utc_Value.utc_wSecond)
End Function

'===============================================================================
Public Function TimestampToLocal(st As String) As Date
    TimestampToLocal = UTCtoLocal((Val(st) / 86400) + 25569)
End Function
Public Function LocalToTimestamp(dt As Date) As String
    LocalToTimestamp = (LocalToUTC(dt) - 25569) * 86400
End Function

Public Function UTCtoLocal(utc_UtcDate As Date) As Date
    On Error GoTo errorUTC
    Dim utc_TimeZoneInfo As utc_TIME_ZONE_INFORMATION, utc_LocalDate As utc_SYSTEMTIME
    utc_GetTimeZoneInformation utc_TimeZoneInfo
    utc_SystemTimeToTzSpecificLocalTime utc_TimeZoneInfo, utc_DateToSystemTime(utc_UtcDate), utc_LocalDate
    UTCtoLocal = utc_SystemTimeToDate(utc_LocalDate)
    Exit Function
errorUTC:
    Debug.Print "UTC parsing error: " & Err.Number & " - " & Err.Description: Stop
End Function

Public Function LocalToUTC(utc_LocalDate As Date) As Date
    On Error GoTo errorUTC
    Dim utc_TimeZoneInfo As utc_TIME_ZONE_INFORMATION, utc_UtcDate As utc_SYSTEMTIME
    utc_GetTimeZoneInformation utc_TimeZoneInfo
    utc_TzSpecificLocalTimeToSystemTime utc_TimeZoneInfo, utc_DateToSystemTime(utc_LocalDate), utc_UtcDate
    LocalToUTC = utc_SystemTimeToDate(utc_UtcDate)
    Exit Function
errorUTC:
    Debug.Print "UTC conversion error: " & Err.Number & " - " & Err.Description: Stop
End Function

^{I know this seems like an awful lot of code just to add/subtract a few hours from a time, but I painstakingly researched, hoping to find a reliable shorter/easier method that's guaranteed to be accurate with both current and historical times, but was unsuccessful. All that's needed to use this method is a copy & paste. ☺}

---

Example usage:

Sub testTZC()
'(Note that "Local time" in these examples is Vancouver/Los Angeles)
    MsgBox LocalToUTC("2004-04-04 01:00") 'returns: 2004-04-04 9:00:00 AM (not DST)
    MsgBox LocalToUTC("2004-04-04 03:00") 'returns: 2004-04-04 10:00:00 AM (is DST)
    MsgBox UTCtoLocal("2000-01-01 00:00") 'returns: 1999-12-31 4:00:00 PM
    MsgBox TimestampToLocal("1234567890") 'returns: 2009-02-13 3:31:30 PM
    MsgBox LocalToTimestamp("April 17, 2019 7:45:55 PM") 'returns: 1555555555
End Sub

Kersten

This doesn't seem to parse ISO8601 time with time zone information correctly; when I enter "2022-07-05T12:20:23.473-04:00" it gives me an wrong value: debug.Print TimestampToLocal("2022-07-05T12:20:23-04:00") returns :12/31/1969 7:33:42 PM – Pacesetter

Answer 3

As mentioned by @ashleedawg calling LocalFileTimeToFileTime may return an unexpected result since it uses the current settings for the time zone and daylight saving time.

To convert a local time to an UTC time and vice versa by calling SystemTimeToTzSpecificLocalTime and TzSpecificLocalTimeToSystemTime which accounts for the daylight saving time:

Private Declare PtrSafe Function GetTimeZoneInformation Lib "kernel32" (lpTimeZoneInformation As Any) As Long
Private Declare PtrSafe Function SystemTimeToTzSpecificLocalTime Lib "kernel32" (lpTimeZoneInformation As Any, lpUniversalTime As Any, lpLocalTime As Any) As Long
Private Declare PtrSafe Function TzSpecificLocalTimeToSystemTime Lib "kernel32" (lpTimeZoneInformation As Any, lpLocalTime As Any, lpUniversalTime As Any) As Long
Private Declare PtrSafe Function SystemTimeToVariantTime Lib "OleAut32" (lpSystemTime As Any, pvtime As Any) As Long
Private Declare PtrSafe Function VariantTimeToSystemTime Lib "OleAut32" (ByVal pvtime As Double, lpSystemTime As Any) As Long


Public Function LocalTimeToUtc(ByVal Value As Date) As Date
  Dim tm(0 To 15) As Integer, tzi(0 To 171) As Byte
  GetTimeZoneInformation tzi(0)
  VariantTimeToSystemTime Value, tm(0)
  TzSpecificLocalTimeToSystemTime tzi(0), tm(0), tm(8)
  SystemTimeToVariantTime tm(8), LocalTimeToUtc
End Function

Public Function UtcToLocalTime(ByVal Value As Date) As Date
  Dim tm(0 To 15) As Integer, tzi(0 To 171) As Byte
  GetTimeZoneInformation tzi(0)
  VariantTimeToSystemTime Value, tm(0)
  SystemTimeToTzSpecificLocalTime tzi(0), tm(0), tm(8)
  SystemTimeToVariantTime tm(8), UtcToLocalTime
End Function

Public Function LocalTimeToUnixTime(ByVal Value As Date) As Double
  LocalTimeToUnixTime = (LocalTimeToUtc(Value) - 25569) * 86400
End Function

Public Function UnixTimeToLocalTime(ByVal Value As Double) As Date
  UnixTimeToLocalTime = UtcToLocalTime(Value / 86400 + 25569)
End Function

Cense

This method is not recommended as it can't be trusted for accuracy beyond "today's date", since LocalFileTimeToFileTime uses the current settings for the time zone and daylight saving time. Therefore, if it is currently daylight saving time, this function will add/subtract DST, even if the time you are converting is in standard time. There are other situations for specific times zones that this method will also convert inaccurately. I'll post an answer with using a similar method but I've found to be 100% accurate in all situations. – Kersten

Answer 4

Here is a core function from my project on maximising the usage of timezone information in Windows.

' Converts a present UtcDate from Coordinated Universal Time (UTC) to local time.
' If IgnoreDaylightSetting is True, returned time will always be standard time.
'
' For dates not within the current year, use the function DateFromDistantUtc.
'
' 2017-11-27. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function DateFromUtc( _
    ByVal UtcDate As Date, _
    Optional ByVal IgnoreDaylightSetting As Boolean) _
    As Date

    Dim LocalBias   As Long
    Dim LocalDate   As Date

    ' Find the local time using standard bias.
    LocalBias = LocalBiasTimezonePresent(UtcDate, True)
    LocalDate = DateRemoteBias(UtcDate, UtcBias, LocalBias)
    If IgnoreDaylightSetting = False Then
        ' The local time should be returned as daylight time.
        If IsCurrentDaylightSavingTime(LocalDate) Then
            ' The local time is daylight time.
            ' Find bias for daylight time.
            LocalBias = LocalBiasTimezonePresent(LocalDate, IgnoreDaylightSetting)
            ' Find the local time using daylight bias.
            LocalDate = DateRemoteBias(UtcDate, UtcBias, LocalBias)
        End If
    End If

    DateFromUtc = LocalDate

End Function

It calls a series of supporting functions which will be too much to post here. The project including full code and demos for Access and Excel can be found on GitHub: VBA.Timezone-Windows

Lauretta