Convert milliseconds timestamp into Excel date and time

Published August 3rd, 2011

If you have a CSV file containing timestamps as milliseconds then this formula will convert into Excel date time

Assuming your millisecond timestamp is in cell A1

=(A1/1000 + ("1/1/1970"-"1/1/1900"+1)*86400) / 86400

or if you have rows with missing timestamps:

=IF(A1="", "", (A1/1000 + ("1/1/1970"-"1/1/1900"+1)*86400) / 86400)

If you are using UNIX timestamps which only have 1 second accuracy then change

A1/1000 + ...

A1 + ...

Adapted from a solution on www.mrexcel.com/forum/showthread.php?t=130691

At 2017-12-22 07:03:00, Andy left the following comment ...
Thank you thank you thank you! I've been trying forever to make formulas I've found on the net work, and none of them did. You get a gold star. :-)

At 2018-06-15 15:02:00, Peter left the following comment ...
This is the right formula, thanks a lot! Took me half a day to find your page.