In VBA, How to convert a UTC UNIX timestamp to Local timezone Date in a simple way?

Question

As I know, we can use below to roughly convert a UNIX timestamp to a VB Date

CDate([UNIX timestamp]/ 60 / 60 / 24) + "1/1/1970"

However, the time zone and daylight information are not considered.

Time zone is not a big deal. But I cannot get the daylight bias information for a specific UNIX timestamp.

Though, daylight bias of Date 1/1 is obviously different from Date 6/1, however, for Date 3/12 or Date 11/5, the daylight bias calculation is very complex.

I tried several APIs, like “FileTimeToLocalFileTime” and “GetTimeZoneInformation” , but none of them work.

Here is my code that can not handle daylight bias

Option Explicit

#If VBA7 Then
  Private Declare PtrSafe Function LocalFileTimeToFileTime Lib "kernel32" (src@, tgt@) As Long
  Private Declare PtrSafe Function FileTimeToLocalFileTime Lib "kernel32" (src@, tgt@) As Long
#Else
  Private Declare Function LocalFileTimeToFileTime Lib "kernel32" (src@, tgt@) As Long
  Private Declare Function FileTimeToLocalFileTime Lib "kernel32" (src@, tgt@) As Long
#End If

Public Function ToUTC(ByVal datetime As Date) As Date
  Dim ftLoc@, ftUtc@
  ftLoc = (datetime - #1/1/1601#) * 86400000
  LocalFileTimeToFileTime ftLoc, ftUtc
  ToUTC = ftUtc / 86400000# + #1/1/1601#
End Function

Public Function FromUTC(ByVal datetime As Date) As Date
  Dim ftUtc@, ftLoc@
  ftUtc = (datetime - #1/1/1601#) * 86400000
  FileTimeToLocalFileTime ftUtc, ftLoc
  FromUTC = ftLoc / 86400000# + #1/1/1601#
End Function

Function getDateFromTimestamp(ByVal value) As Date
    Dim t1, t2
    t1 = CDate(value / 60 / 60 / 24) + "1/1/1970"
    t2 = FromUTC(t1)
    Debug.Print t2 - t1
    getDateFromTimestamp = t2
End Function

Answer 1

Probably the easiest way is via COM Interop to use the DateTime APIs built in to .NET. There's a good intro to this approach in this answer.

Create a C# class library. To make things simpler, target .NET Framework 4.6 or higher, so you can use the FromUnixTimeSeconds and ToUnixTimeSeconds methods on the DateTimeOffset class.

using System;
using System.Runtime.InteropServices;

namespace MyDateTimeLibrary
{
    [Serializable]
    [ComVisible(true)]
    [ClassInterface(ClassInterfaceType.AutoDual)]
    public class DateTimeFunctions
    {
        public DateTime UnixTimeToDateTime(int unixTimeInSeconds)
        {
            return DateTimeOffset.FromUnixTimeSeconds(unixTimeInSeconds).UtcDateTime;
        }

        public long DateTimeUtcToUnixTime(DateTime utcDateTime)
        {
            return new DateTimeOffset(utcDateTime, TimeSpan.Zero).ToUnixTimeSeconds();
        }

        public DateTime UtcToLocal(DateTime utcDateTime)
        {
            return utcDateTime.ToLocalTime();
        }

        public DateTime LocalToUtc(DateTime localDateTime)
        {
            return localDateTime.ToUniversalTime();
        }

        public DateTime TZSpecificDateTimeToUTC(DateTime sourceDateTime, string sourceTimeZoneId)
        {
            var tzi = TimeZoneInfo.FindSystemTimeZoneById(sourceTimeZoneId);
            return TimeZoneInfo.ConvertTimeToUtc(sourceDateTime, tzi);
        }

        public DateTime UTCToTZSpecificDateTime(DateTime utcDateTime, string destinationTimeZoneId)
        {
            var tzi = TimeZoneInfo.FindSystemTimeZoneById(destinationTimeZoneId);
            return TimeZoneInfo.ConvertTimeFromUtc(utcDateTime, tzi);
        }
    }
}

For what you asked, you really only need the UnixTimeToDateTime and UtcToLocal methods from the above code, but I thought I'd throw a few more in for others that may be in similar situations. Really, anything you can do in .NET you can do here. The .NET DateTime, DateTimeOffset and TimeZoneInfo APIs are much easier and more reliable than the Win32 APIs you would need to do similar work.

Check the Register for COM interop option before you build (as mentioned in the answer linked earlier), or otherwise register your DLL with COM via the RegAsm.exe utility if you are deploying to another machine.

Next, add a reference from your VBA project to your newly registered MyDateTimeLibrary. Then use it, like so (for example):

Sub Test()

    Dim dtf As New MyDateTimeLibrary.DateTimeFunctions
    Dim utcDt As Date, localDt As Date

    utcDt = dtf.UnixTimeToDateTime(1487010504)
    localDt = dtf.UtcToLocal(utcDt)

    MsgBox ("UTC: " + CStr(utcDt) + vbCrLf + "Local: " + CStr(localDt))

    Set dtf = Nothing

End Sub

I'm sure you can refactor this to write some useful VBA functions that pass in and out the dates to convert, call those from Excel cells, or whatever your context is. I'll leave that part up to you.