22

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?

37

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
5
  • Very nice, I think this is actually a better answer so I'm going to except it… Thanks!
    – JVC
    Dec 11 '17 at 21:54
  • Yeah this doesn't seem to be working for me, it outputs some weird date. Any idea why?
    – Jay
    Feb 16 '20 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.
    – Dave L.
    Feb 19 '20 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. Sep 24 '20 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.
    – Dave L.
    Sep 24 '20 at 16:04
11

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.
2
  • 1
    As the answer by mbican notes, this formula is off by 1 day. xkcd.com/386
    – Dave L.
    Dec 11 '17 at 17:22
  • The answer is fixed now.
    – Dave L.
    Dec 12 '17 at 4:11
5

The result was one day ahead for me, so I changed it to =to_date((A1+2209161600)/86400)

1

In Google Sheets

A1 -> cell with *nix timestamp

Date Formula =(((A1/60)/60)/24)+DATE(1970;1;1)

-2

If you want the Easiest Method to Convert Timestamp to Human Readable Dates then try this Timestamp Converter Tool

1
  • Please note that question was about Google Sheets
    – Kos
    Sep 26 '19 at 20:08

Not the answer you're looking for? Browse other questions tagged or ask your own question.