Converting timestamps to dates formula

In Apps Script, it's easy 

var timeStamp = new Date().getTime();
var date = new Date(timestamp);

But sheets doesn't use JavaScript timestamps for dates, it uses the number of days since 1st Jan 1900, with hours/mins/secs represented as a fraction of a day (an idea copied from Excel - known as DateValue + TimeValue) - so all these are equivalent

 datetimestamp  datevalue+timevalue
 20/05/2015 11:31:05 1432121465149 42144.4799189815

The Epoch

Unix-like systems use 1st Jan 1970 as zero hour (often referred to as the epoch) for dates, so a JavaScript timestamp (13 digits)  is  the number of milliseconds since then. Prior to then is a negative number. Unix timestamps (10 digit) use the same base, but are the number of seconds rather than milliseconds.

Knowing these two base dates makes converting back and forwards with a Sheets (or Excel) formula easy. 

formula for converting a JS timestamp to a date

=yourTimestamp/1000/60/60/24 + date (1970,1,1)

formula for converting a date to a JS timestamp

=(yourDate-date(1970,1,1))*1000*60*60*24

For more like this, see Google Apps Scripts snippets. Why not join our forum, follow the blog or follow me on twitter to ensure you get updates when they are available. 

You want to learn Google Apps Script?
Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly. 
If you prefer Video style learning I also have two courses available. also published by O'Reilly.
 Google Apps Script for Developers and Google Apps Script for Beginners.





Comments