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.

Answer 1

=(((B2 / 1000) / 86400) + DATE(1970, 1, 1)) + (10/24).

Explanation: B1 / 1000: Converts milliseconds to seconds. / 86400: Converts seconds to days, since Excel stores dates as days.

• DATE(1970,1,1): Sets the start date at Unix epoch (January 1, 1970).
• (10/24): Adds 10 hours for Australian Eastern Standard Time (AEST).

Ensure Cell Formatting:

The value in C1 will now be an Excel date-time value, but it might not be displaying correctly due to cell formatting.

Right-click on cell C1, select "Format Cells."

Choose "Custom" and enter:

yyyy-mm-dd hh:mm:ss

you can customised as per your need like format.

Answer 2

This works for me with specified GMT.

=((epoch_time_in_ms/1000)+8*3600)/86400 + 25569

Where +8 is the time zone.

For example:

GMT : +8 hours

This determines whether to add or minus the time difference

My GMT is plus 8 hours in seconds then need to add 8 x 60 x 60

Format Cell : Custom > d/m/yyyy hh:mm

epoch_time_in_ms = 1688461727938

Final value = 4/7/2023 17:17

Answer 3

Just point and shoot.
Replace the C2 with your cell no. No need to format your Excel cell.
Also, you can use this unixtimestamp website to verify your data.

International format (ISO 8601):

=TEXT(C2/(1000*60*60*24)+25569,"YYYY-MM-DD HH:MM:SS")

2022-10-20 00:04:22  
2022-10-20 00:05:20  
2022-10-20 00:14:58  

US format:

=TEXT(C2/(1000*60*60*24)+25569,"MM/DD/YYYY HH:MM:SS")

10/20/2022 00:04:22  
10/20/2022 00:05:20  
10/20/2022 00:14:58  

Europe format:

=TEXT(C2/(1000*60*60*24)+25569,"DD.MM.YYYY HH:MM:SS")

20.10.2022 00:04:22  
20.10.2022 00:05:20  
20.10.2022 00:14:58

If you only need the date, remove the 'HH:MM:SS'.

=TEXT(C2/(1000*60*60*24)+25569,"YYYY-MM-DD")

Answer 4

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/1900 UTC/GMT. So 25569 corresponds to 1/1/1970 00:00 UTC/GMT and 25570 to 2/1/1970 00:00.

Now if you also note that we have 86400 seconds per day (24 hours x60 minutes x60 seconds) and you will understand what this formula does: A1/86400 converts seconds to days and +25569 adjusts for the offset between what is zero-time for Unix and what is zero-time 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 milliseconds since the the Unix epoch.

Answer 5

You are seeing the date as ######## most likely because by definition the EPOCH times is in seconds - https://en.wikipedia.org/wiki/Unix_time. This means the number should be 10 characters long. Your number has 13 characters (see 1504865618099) and it is most likely in milliseconds (MS). In order to fix the formula just divide the number by 1000. Just keep in mind this way you'll loose the MS precision, but in most cases this is OK. So the final formula should be:

=A1/(86400 * 1000) + DATE(1970,1,1)

Answer 6

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

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

+1*3600 is GTM+1

Answer 7

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

Answer 8

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

Answer 9

• 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