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?
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
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).
- 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.