12

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?

17

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
  • Very nice, I think this is actually a better answer so I'm going to except it… Thanks! – Jonathan van Clute Dec 11 '17 at 21:54
7

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.
  • 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
3

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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