How do I convert Unix timestamps to readable dates in a spreadsheet?

Excel and Google Sheets treat dates as serial numbers, so a raw Unix timestamp looks like gibberish. Convert with:

Google Sheets:

Code:

=A2/86400 + DATE(1970,1,1)

then format the cell as date/time. (86400 = seconds in a day.)

Excel: same formula, but watch the timezone – Excel uses your local offset:

Code:

=(A2/86400) + DATE(1970,1,1)

If your data is UTC and your sheet is local, add the offset explicitly.

Milliseconds? Divide by 1000 first.

Pro tip: keep the raw timestamp column and the formatted one side by side – you'll thank yourself later.

Do you mostly deal with seconds or millisecond timestamps?