Excel - Convert JavaScript/Unix timestamp to date

user_1911755 0
Viewed 4918

I'm wondering how to convert a timestamp to a date in Excel.

JavaScript timestamp:

1486812409161

Unix timestamp

1486812409

What Excel function / formula can convert to something like:

2/11/2017 11:26 AM (or any human readable date)

I did see this answer, but I can't get this to work for me (on Mac OS X / Excel 2011).

When I create a new cell and set it's value to the following formula:

= (MsValueCellReference / 86400000) + DATE(1970,1,1)

The result is: 41315.47696

excel
2 Answers

I am using the current formula =(<javascriptTimestamp>)/(1000*60*60*24)+25569 and then formatting the cell with dd/mm/yyyy hh:mm:ss.

To explain the terms in the formula, the Javascript timestamp has milliseconds which accounts for the 1000. There are 60*60*24 seconds in one day.

Finally, Excel dates start on Jan 1 1900, and Javascript starts on Jan 1 1970. There are 25569 days between Jan 1 1900 and Jan 1 1970.

user_36072730
Related