Excel vba : Convert Unix Timestamp to Date Time

Question

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.

FaneDuru · Accepted Answer · 2022-07-17 14:55:17Z

7

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

edited Jul 17, 2022 at 14:55

answered Jul 17, 2022 at 14:41

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Amy Over a year ago

Thank you. Just combined your functions to:

Function FromUNIX(uT) As Date    If Len(uT) = 10 Then         FromUNIX = fromUnix10(uT)    ElseIf Len(uT) = 13 Then         FromUNIX = fromUNIX13Digits(uT)    Else         MsgBox "No UNIX timestamp passed to be converted..."    End If End Function

David Over a year ago

We could also join the two functions into one with an exponentiation operation:

Public Function fromUnix(ts) As Date     fromUnix = CDbl(uT) / (86400 * (10 ^ (VBA.Len(ts) - 10))) + DateSerial(1970, 1, 1) End Function

FaneDuru Over a year ago

@David Yes, but if you call the function using a String number different of 10 or 13, it will return something strange, not sending a message about the input incompatibility... It can be adapted to return Variant and using IIf to return the error, but it will make you asking "why simple when complicated is possible". :)

David Over a year ago

10 vs 13 is due to the use or not of milliseconds in the UNIX timestamp. Other values are not expected, right? In any case, it seems that there is no problem in using a string number of another size; try it.

Waleed Apr 28 at 8:16

Are you still a live?

|

Collectives™ on Stack Overflow

Excel vba : Convert Unix Timestamp to Date Time

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related