# 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.

## 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`.

• 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

### 2 thoughts on “Google Sheets: Convert Unix Timestamps to Dates”

1. How to implement This code to our JSON API!