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.
- 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)
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).
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
Convert list of UNIX timestamps to Sheets dates
As before, if your UNIX timestamps are in milliseconds, change the argument from
- When you use an array formula, just enter it once. It will automatically apply itself to all referenced values.
EPOCHTODATEresults in a number like 44995, use the Format menu to view it as a date.
EPOCHTODATEconverts timestamps to UTC, not the local time zone of your spreadsheet