# Dealing with Unix timestamps in Excel

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

(function() {
var po = document.createElement(‘script’); po.type = ‘text/javascript’; po.async = true;