Google Sheets: Convert Unix Timestamps to Dates

Certain APIs and datasets accept or display data in Unix epoch timestamps, which are not natively recognized by Google Sheets and can therefore not be used for calculations or other data transformations.

These timestamps can be converted as follows, substituting in your date cell where you see A2. Array formulas are also provided for converting an entire list at once.

Contents

• Convert Sheets date to UNIX timestamp
• Convert list of Sheets dates to Unix timestamps (array formula)
• Convert UNIX timestamp to Sheets date
• Convert list of UNIX timestamps to Sheets dates (array formula)
• Notes

Convert Sheets date to UNIX timestamp

=(A2 - DATE(1970, 1, 1))*86400

Convert list of Sheets dates to UNIX timestamps

=arrayformula(if(A2:A<>"",(A2:A - DATE(1970, 1, 1))*86400,""))

The array formula only needs to be entered once at the top, as it will automatically apply itself to all values in the column.

Convert UNIX timestamp to Sheets date

An earlier version of this article included the function =A2/ 86400 + DATE(1970, 1, 1) here. In February 2023, Google introduced the EPOCHTODATE function, so this article has been updated to use that instead. (There's still no DATETOEPOCH function, so it only works in one direction).

=EPOCHTODATE(A2,1)

In the above example, the UNIX timestamp has a time unit of seconds (you can tell because it has 10 digits), so the unit argument is set to 1. In some cases, the UNIX timestamp will instead have a time unit of milliseconds (in which case it will have 13 digits). To account for a timestamp in milliseconds, simply change the unit argument to 2, e.g. =EPOCHTODATE(A2,2)

Convert list of UNIX timestamps to Sheets dates

=arrayformula(if(A2:A<>"",EPOCHTODATE(A2:A,1),""))

As before, if your UNIX timestamps are in milliseconds, change the argument from 1 to 2.

Notes

• When you use an array formula, just enter it once. It will automatically apply itself to all referenced values.
• If EPOCHTODATE results in a number like 44995, use the Format menu to view it as a date.
• EPOCHTODATE converts timestamps to UTC, not the local time zone of your spreadsheet
• EPOCHTODATE documentation: https://support.google.com/docs/answer/13193461