Excel vba : Convert Unix Timestamp to Date Time-CodePudding

I know this has been asked quite a bit, but for some reason none of the solutions seem to work for me.

I have a unix time stamp (for example purposes use 1637402076084)

On my excel sheet I can convert that fine using = (C2/ 86400000) DATE(1970,1,1)

However I want to do this in my VBA code as there's a lot of data and I prefer doing it all in an array then applying my array to the sheet (for performance purposes)

However in my code I'm trying to use dateAdd

DateAdd("s", 1637402076084#, "1/1/1970 00:00:00")

but I get an overflow error

I'm assuming this is because the unix timestamp is too big? But it's one that gets returned from an api call and is a geniune one (as demonstrated by the formula) but I'm not sure why my code isn't working.

CodePudding user response：

There are two types of UNIX timestamps. 10 digits and 13 digits. The function you try using is for 10 digits type. To convert the 13 digits type, you should create another function, exactly as you use in the cell:

Function fromUNIX13Digits(uT) As Date
   fromUNIX13Digits = CDbl(uT) / 86400000   DateSerial(1970, 1, 1)
End Function

And test it as:

Sub testFromUNIX13()
      Debug.Print fromUNIX13Digits("1637402076084")
End Sub

For 10 digits type, you can use:

Public Function fromUnix10(ts) As Date
    fromUnix10 = DateAdd("s", CDbl(ts), "1/1/1970")
End Function

And test it using:

Sub testFromUnix10()
   Debug.Print fromUnix10("1651680385")
   Debug.Print Format(fromUnix10("1651680385"), "yyyy/mm/dd hh:nn:ss")
End Sub

Or build a function able to deal with both types:

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

And test it using:

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