Search code examples
excelvbaunix-timestamp

Unix timestamp to Excel datetime

How to convert the Unix timestamp(1662091200) to Windows timestamp format in Excel? VBA code might be helpful.

Solution

  • UNIX time can be expressed in two ways (10 and 13 digits). Try the next function, able do deal with both cases:

    Function FromUNIX(uT) As Date
   If Len(uT) = 10 Then
        FromUNIX = DateAdd("s", CDbl(uT), "1/1/1970")
   ElseIf Len(uT) = 13 Then
        FromUNIX = CDbl(uT) / 86400000 + DateSerial(1970, 1, 1)
   Else
        MsgBox "No UNIX timestamp passed to be converted..."
   End If
End Function

    It can be tested as

    Sub testFromUnix_()
 Dim x As String, y As String, z As Double
 x = "1637402076084"
 y = "1662091200"
 z = 1662091200
 
 Debug.Print FromUNIX(x)
 Debug.Print FromUNIX(y)
 Debug.Print FromUNIX(y)
End Sub

    You can use it as UDF:

       =FromUNIX(A2)

    Of course, in A2 should be the UNIX time...