19

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?

27

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
| improve this answer | |
  • 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? – JayPex Feb 16 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 at 18:03
8

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.
| improve this answer | |
  • 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
4

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

| improve this answer | |
1

In Google Sheets

A1 -> cell with *nix timestamp

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

| improve this answer | |
-2

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

| improve this answer | |
  • Please note that question was about Google Sheets – Kos Sep 26 '19 at 20:08

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.