![Caution!](https://i.stack.imgur.com/nyyul.png)
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
![Solution!](https://i.stack.imgur.com/ktbSP.png)
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