Excel - Convert JavaScript/Unix timestamp to date

Question

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

Answer 1

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.

Answer 2

Excel is pretty happy to interconvert between dates and numbers, as you noticed. However, afaict, it can't always guess correctly which of the two you want to see.

So, to ensure that a value is rendered as a date, you'd need to open the Format Cells dialog, go to the first tab Number, and set the cell's format Category to one of the Date types.