通过 Excel 将 unix 时间转换为日期时间

试图将1504865618099.00 Unix 时间转换为可读日期时间。 我试过了:

=(UNIX + ("1/1/1970"-"1/1/1900"+1)*86400) / 86400

但是没有用。

179172 次浏览
小开

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

小开

  • 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

小开

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

小开

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

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

+1*3600 is GTM+1

小开

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.

小开

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)
小开

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

提交答案