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.

Henequen

Looks like your timestamp is equal to 04/17/49657 @ 3:14am. Is this correct? Somehow the formula that I always use doesn't work with this. EDIT: Turns out excels max date is December 31, 9999 – Dorcus

yeah, it should be that format. But I can't convert it in Excel. What formula do you use? – Henequen

I use =(A1/86400)+25569, which works fine. Just make sure to set the output cell to a Date type. – Dorcus

mhh, I tied this but I only get #####...#### – Henequen

btw, I'm using Excel on Mac – Henequen

#####...#### means the value is too large for excel.The max date is 31-12-9999. This value is large than that, so this is why it doens't work. – Dorcus

mhh ok. Any other suggestions? I think it's only the date – Henequen

Answer 1

• 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

Pindling

How is this not impacted by time zones / locales? – Grouping

@MichaelMol Alway epoch timestamp are always GMT read here:#2854477 – Lasley

why use left and right when you can =A1/1000? – Dmitri

Answer 2

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

Dorcus

This worked for me, but only when I replaced the semicolons in the DATE function with commas. – Titanism

@StevenRands Whether Excel uses semicolons or commas in formulas depends on the regional settings. In most European countries it's the semicolon, for most of the rest of the world it's the comma. You can change this in the Windows settings. – Sedlik

Answer 3

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.

Willyt

I needed to add a couple of zeros onto the 86400 divisor to get the correct date - wish I could upvote you a second time for that helpful note in your answer. – Catenane

Thanks for the clear layout of this answer, not just the numbers. @Henequen it's been 4 years. Accept this answer :D – Bailee

@ndemou, a clarification request: I believe Excel's day 0 is 24h before 1900-01-01 at 00:00 UTC, which displays rather confusingly to us humans as 1900-01-00 00:00. Did you possibly mean to type "1/1/1900"? – Cuirassier

@Cuirassier I think you are right: learn.microsoft.com/en-us/office/troubleshoot/excel/… – Willyt

Thank you. My export has a timestamp in milliseconds. So, another three zeros did the job. – Beak

Answer 4

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

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

+1*3600 is GTM+1

Anhinga

Answer 5

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")

Gazo

Thanks, this worked as it should in the first go. – Salman

Answer 6

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

Runner

Needing to view Unix time in Excel I too found it simpler to excise the nanosecond values from the time string. I also used a custom format for the resulting cells to display the data in the manner I wanted. RRK's top answer says all of this but much more as well and sometimes all we need is a bare bones answer. Thanks GuillaumeLabs, +1 – Circus

Answer 7

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)

Wayfarer

Answer 8

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

Contradistinguish

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Regulable