Converting unix time into date-time via excel

Question

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

User · Answer

To convert the epoch Unix-Time  to regular time like for the below timestamp  Ex  1517577336206 First convert the value with the following function like below   LEFT A1 10   amp       amp  RIGHT A1 3  The output will be like below  Ex  1517577336 206 Now Add the formula like below      B1 60  60  24  DATE 1970 1 1  Now format the cell like below or required format Custom format   m d yyyy h mm ss 000   Now example time comes like      2 2 2018 13 15 36 206   The three zeros are for milliseconds

User · Answer

A1  24 60 60    DATE 1970 1 1  should work with seconds     A1 86400 1000  25569 if your time is in milliseconds  so dividing by 1000 gives use the correct date  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

User · Answer

TLDR    A1 86400  25569       and the format of the cell should be date   If it doesn t work for you   If you get a number you forgot to format the output cell as a date  If you get       you probably don t have a real Unix time  Check your timestamps in https   www epochconverter com   Try to divide your input by 10  100  1000 or 10000   You work with timestamps outside Excel s  very extended  limits  You didn t replace A1 with the cell containing the timestamp  -p     Explanation  Unix system represent a point in time as a number  Specifically the number of seconds  since a zero-time called the Unix epoch which is 1 1 1970 00 00 UTC GMT  This number of seconds is called  Unix timestamp  or  Unix time  or  POSIX time  or just  timestamp  and sometimes  confusingly   Unix epoch    In the case of Excel they chose a different zero-time and step  because who wouldn t like variety in technical details    So Excel counts days since 24 hours before 1 1 0000 UTC GMT  So 25569 corresponds to 1 1 1970 00 00 UTC GMT and 25570 to 2 1 1970 00 00    Now please note that we have 86400 seconds per day  24 hours x60 minutes each x60 seconds  and you can understand what this formula does  A1 86400 converts seconds to days and  25569 adjusts for the offset between what is time-zero for Unix and what is time-zero for Excel   By the way DATE 1970 1 1  will helpfully return 25569 for you in case you forget all this so a more  self-documenting  way to write our formula is     A1  24 60 60    DATE 1970 1 1   P S   All these were already present in other answers and comments just not laid out as I like them and I don t feel it s OK to edit the hell out of another answer        that s almost correct because you should not count leap seconds      E g  in the case of this question the number was number of milliseconds since the the Unix epoch

User · Answer

If you have           it can help you      A1 1000 1 3600  86400 25569     1 3600 is GTM 1

User · Answer

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

[excel] Converting unix time into date-time via excel

Examples related to excel

Examples related to datetime

Examples related to unix-timestamp

Examples related to date-conversion