# How to convert Unix Timestamp to human readable?

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
``````
• 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

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

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

Date Formula `=(((A1/60)/60)/24)+DATE(1970;1;1)`
{"ft_lang_label":"__label__en","ft_lang_prob":0.92113996,"math_prob":0.8790779,"size":1868,"snap":"2019-43-2019-47","text_gpt3_token_len":525,"char_repetition_ratio":0.11051502,"word_repetition_ratio":0.0,"special_character_ratio":0.3217345,"punctuation_ratio":0.11547912,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.96969354,"pos_list":[0],"im_url_duplicate_count":[null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2019-11-15T12:21:00Z\",\"WARC-Record-ID\":\"<urn:uuid:d8e9d417-b08a-408f-b5e3-d9261776e328>\",\"Content-Length\":\"162274\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:aad1d3d4-4f6b-40e0-96cf-4f92e17e8861>\",\"WARC-Concurrent-To\":\"<urn:uuid:a761d785-2bd4-43cf-a88c-81e9c45cd248>\",\"WARC-IP-Address\":\"151.101.193.69\",\"WARC-Target-URI\":\"https://webapps.stackexchange.com/questions/108115/how-to-convert-unix-timestamp-to-human-readable/134993\",\"WARC-Payload-Digest\":\"sha1:AVN2SB5QFW2W34MX4YSPZITD7CVMUC7A\",\"WARC-Block-Digest\":\"sha1:GLBFQO2N2OWEZXHFTA43N4VFJSTPXNXG\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2019/CC-MAIN-2019-47/CC-MAIN-2019-47_segments_1573496668644.10_warc_CC-MAIN-20191115120854-20191115144854-00275.warc.gz\"}"}