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
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