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.

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 — Pim, Sep 09 '17 at 11:44
yeah, it should be that format. But I can't convert it in Excel. What formula do you use? — JohnSmith, Sep 09 '17 at 11:49
I use `=(A1/86400)+25569`, which works fine. Just make sure to set the output cell to a `Date` type. — Pim, Sep 09 '17 at 11:50
#####...#### 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. — Pim, Sep 09 '17 at 12:26

score 105 · Answer 1 · edited Nov 14 '18 at 13:01

105

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

edited Nov 14 '18 at 13:01

answered Feb 05 '18 at 07:07

Ravikiran Reddy Kotapati

2,485
3
22
27

How is this not impacted by time zones / locales? – Michael Mol Oct 19 '18 at 01:37
1

@MichaelMol Alway epoch timestamp are always GMT read here:https://stackoverflow.com/questions/2853977/unix-timestamp-always-in-gmt – Nithi2023 Nov 09 '18 at 06:48
3

why use ```left``` and ```right``` when you can ```=A1/1000```? – sutan Sep 01 '22 at 09:54

score 50 · Answer 2 · edited Jan 19 '20 at 18:49

50

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

edited Jan 19 '20 at 18:49

LWC

1,084
1
10
28

answered Sep 09 '17 at 11:41

Pim

850
7
17

5

This worked for me, but only when I replaced the semicolons in the DATE function with commas. – Steven Rands Aug 01 '19 at 08:34
5

@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. – teylyn Aug 16 '19 at 22:12

ndemou · Answer 3 · 2022-09-24T23:56:48.317

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.

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. — cssyphus, Mar 14 '21 at 19:34
Thanks for the clear layout of this answer, not just the numbers. @JohnSmith it's been 4 years. Accept this answer :D — KobeJohn, Dec 04 '21 at 03:54
@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"? — Neman, Sep 22 '22 at 20:24
@Neman I think you are right: https://learn.microsoft.com/en-us/office/troubleshoot/excel/1900-and-1904-date-system — ndemou, Sep 24 '22 at 23:55

score 7 · Answer 4 · edited Feb 28 '20 at 13:59

7

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

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

+1*3600 is GTM+1

edited Feb 28 '20 at 13:59

xiawi

1,772
4
19
21

answered Feb 28 '20 at 13:18

Шахзод

121
2
3

score 2 · Answer 5 · answered Dec 11 '18 at 10:29

2

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

answered Dec 11 '18 at 10:29

OrganicMustard

1,158
1
15
36

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 – user23715 Jun 30 '21 at 17:19

score 1 · Answer 6 · answered Jan 05 '22 at 13:36

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)

DxTx · Answer 7 · 2022-10-22T05:57:50.840

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

Converting unix time into date-time via excel

7 Answers7

TLDR

If it doesn't work for you

Explanation

Linked

Related