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.

 

For more stuff like this see Excel Liberation

About brucemcp 223 Articles
I am a Google Developer Expert and decided to investigate Google Apps Script in my spare time. The more I investigated the more content I created so this site is extremely rich. Now, in 2019, a lot of things have disappeared or don’t work anymore due to Google having retired some stuff. I am however leaving things as is and where I came across some deprecated stuff, I have indicated it. I decided to write a book about it and to also create videos to teach developers who want to learn Google Apps Script. If you find the material contained in this site useful, you can support me by buying my books and or videos.

Be the first to comment

Leave a Reply

Your email address will not be published.


*


17 − 1 =