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

I believe Excel stores its dates as number of days since January 1, 1900. — Tim Biegeleisen
– Tim Biegeleisen, Commented Sep 13, 2017 at 15:09
Well I'll be damned. Formatting as a date works. If you write a formal answer, I'll accept. — Raphael Rafatpanah
– Raphael Rafatpanah, Commented Sep 13, 2017 at 15:10
@TimBiegeleisen Yeah, unless you tick on "Use 1904 date system" in File > Options > Advanced > When calculating this workbook. Which, in my experience, then fixes whatever problem you changed it for, while newly breaking everything else and ultimately not being worth the hassle. :P — underscore_d
– underscore_d, Commented Sep 13, 2017 at 15:14
@underscore_d ... or the fact that Excel skips a day in the first leap after 1900 (I think). — Tim Biegeleisen
– Tim Biegeleisen, Commented Sep 13, 2017 at 15:15

S Meaden · Accepted Answer · 2020-03-25 19:39:17Z

8

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.

edited Mar 25, 2020 at 19:39

answered Jan 11, 2019 at 20:51

S Meaden

8,3203 gold badges39 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

underscore_d · Accepted Answer · 2017-09-13 15:13:03Z

5

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.

answered Sep 13, 2017 at 15:13

underscore_d

6,8734 gold badges46 silver badges75 bronze badges

Collectives™ on Stack Overflow

Excel - Convert JavaScript/Unix timestamp to date

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related