excel - sheets - unix time converter




Converting unix time into date-time via excel (2)

Trying to convert 1504865618099.00 Unix time into a readable date time. I tried this:

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

But it's not working.


=A1/(24*60*60) + DATE(1970;1;1) should work with seconds. Your time is in milliseconds, so dividing by 1000 gives use the correct date (like =(A1/86400/1000)+25569 Don't forget to set the type to Date on your output cell. I tried it with this date: 1504865618099 which is equal to 8-09-17 10:13.


in case the above does not work for you. for me this did not for some reasons;

the UNIX numbers i am working on are from the Mozilla place.sqlite dates.

to make it work : i splitted the UNIX cells into two cells : one of the first 10 numbers (the date) and the other 4 numbers left (the seconds i believe)

Then i used this formula, =(A1/86400)+25569 where A1 contains the cell with the first 10 number; and it worked





date-conversion