As the title says, I have Unix Timestamps on a sheet and I need to convert them to normal human-readable dates. So far I'm striking out, Google searches have turned up a few suggestions but none have worked for me at all. Does anyone have a formula that works for converting these?
Add a comment
|
4 Answers
I prefer a formula that is more transparent, with fewer magic numbers. It makes it easier to see what's going on and also avoids the likelihood of having a bug (like the current top answer to this question which is off by 1 day):
=A1/60/60/24 + DATE(1970,1,1)
And if you want to shift it from UTC to a timestamp that is offset by some number of hours, for example UTC-8:
=A1/60/60/24 + DATE(1970,1,1) - 8/24
Note that if your Unix timestamp is in milliseconds you will need to divide by 1000 first ie.:
=A1/1000/60/60/24 + DATE(1970,1,1) - 8/24
-
Very nice, I think this is actually a better answer so I'm going to except it… Thanks!– JVCCommented Dec 11, 2017 at 21:54
-
Yeah this doesn't seem to be working for me, it outputs some weird date. Any idea why?– JayCommented Feb 16, 2020 at 9:31
-
@JayPex Don't know, but maybe if you gave some more details. One thing to check would be format for that cell.– David LCommented Feb 19, 2020 at 18:03
-
This formula gives me #ERROR, probably due to mixing types. This one: =A2/60/60/24 + DATEVALUE("1970/1/1") works tho. Commented Sep 24, 2020 at 9:01
-
@IgnacioSolerGarcia Very interesting to me that you and JayPex are getting errors when I don't. I wish I could reproduce it. I also see what you mean about mixing types, and don't know the rules that Google Sheets uses for autoconverting between types. For careful conversion, I think perhaps this may be safe: TO_DATE(A1/60/60/24 + N(DATE(1970,1,1))). It would explicitly convert from date to number, add to it, then convert back to a date.– David LCommented Sep 24, 2020 at 16:04
Google Sheets measure date-time in days, with 0 mark being 1899-12-30 0:00:00. This being before 1970, the Unix timestamp for this moment (assuming GMT time) is negative, namely -2209161600. So, the formula
=(A1 + 2209161600)/86400
will convert Unix timestamp in cell A1 into date-time (format the cell with the formula as such).
Limitations:
- Leap seconds are ignored
- The formula is for GMT, while Google Sheets time is in local timezone. To adjust the formula for your time zone, visit https://www.epochconverter.com/ and get the timestamp for 1899-12-30 0:00:00 in your local time.
-
1As the answer by mbican notes, this formula is off by 1 day. xkcd.com/386– David LCommented Dec 11, 2017 at 17:22
-
The result was one day ahead for me, so I changed it to =to_date((A1+2209161600)/86400)
In Google Sheets
A1 -> cell with *nix timestamp
Date Formula =(((A1/60)/60)/24)+DATE(1970;1;1)
