# [excel] 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.

This question is related to `excel` `datetime` `unix-timestamp` `date-conversion`

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

• 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

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

`=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`.

``````=((A1/1000+1*3600)/86400+25569)
`+1*3600` is GTM+1