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.
Today we’ll look at how to deal with the timeStamps you might find in data that has been generated by javaScript or comes from Unix systems.
Unix Time
A Unix timeStamp is the number of seconds or milliseconds since ‘the Unix epoch’, ie. 1st January 1970 00:00:00 = value 0 for coordinated universal time (UTC). This ends up being a 10 digit number if we are counting seconds. Timestamps generated by javascript (new Date().getTime()) will be 13 digits long and include milliseconds.
Excel Time
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
' javaScript Time
d = CDbl(left(s, 10))
' may need to round for milliseconds
If Int(Mid(s, 11, 3) >= 500) Then
d = d + 1
End If
ElseIf (Len(s) = 10) Then
' unix Time
d = CDbl(s)
Else
' wtf time
dateFromUnix = CVErr(xlErrValue)
Exit Function
End If
dateFromUnix = DateAdd("s", d, DateSerial(1970, 1, 1))
End Function
For more stuff like this see Excel Liberation
