If you take data from outside Excel – especially using REST APIS, there’s a good chance that you’ll have to deal with Unix Times. If you are familiar with the Excel Liberation site, you’ll know that a large chunk of it is all about getting JSON data from elsewhere into your Excel Workbook, in the course of which I’ve come across lots of different formats.
Excel is of course different, with its epoch being 1st January 1900. Time is represented as the number of days since that date. Hours, minutes and seconds are held as a fraction of a day (noon would be represented as .5).
Converting from Unix to Excel Time
Knowing this, the conversion becomes rather straightforward, and can be executed by this simple custom function, which adds the number of seconds given by the timestamp to an Excel representation of the Unix Epoch.
Public Function dateFromUnix(s as string) As Date
Dim d As Double
If (Len(s) = 13) Then
d = CDbl(left(s, 10))
' may need to round for milliseconds
If Int(Mid(s, 11, 3) >= 500) Then
d = d + 1
ElseIf (Len(s) = 10) Then
' unix Time
d = CDbl(s)
' wtf time
dateFromUnix = CVErr(xlErrValue)
dateFromUnix = DateAdd("s", d, DateSerial(1970, 1, 1))
For more stuff like this see Excel Liberation