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

`=(A1/86400)+25569`

...and the **format of the cell** should be date.

- 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

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`

.

If you have ########, it can help you:

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

`+1*3600`

is GTM+1

Source: Stackoverflow.com