Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Converting unix time into date-time via excel

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.

like image 440
JohnSmith Avatar asked Sep 09 '17 11:09

JohnSmith


People also ask

How do I convert UNIX timestamp to date in Excel?

Convert timestamp to date If you have a list of timestamp needed to convert to date, you can do as below steps: 1. In a blank cell next to your timestamp list and type this formula =(((A1/60)/60)/24)+DATE(1970,1,1), press Enter key, then drag the auto fill handle to a range you need.

How do I convert UNIX timestamp to local time in Excel?

Since a day contains 86400 seconds (24 hours x 60 minutes x 60 seconds), conversion to Excel time can be done by dividing days by 86400 and adding the date value for January 1st, 1970. When C5 is formatted with the Excel date "d-mmm-yyyy", the date is displayed as 1-Oct-2018.


1 Answers

  • 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) & "." & 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

like image 105
Ravikiran Reddy Kotapati Avatar answered Sep 20 '22 18:09

Ravikiran Reddy Kotapati