How to Use the EPOCHTODATE Function in Google Sheets to Convert Unix Timestamps

Exporting analytics logs, database records, and server events into Google Sheets frequently produces raw timestamps formatted as Unix epoch integers (e.g. 1728562800). In computing, the Unix epoch counts elapsed seconds or milliseconds since 1 January 1970 UTC. Previously, translating these large integer numbers into readable calendar dates required complex division formulas involving 86400 and serial date offsets. Google Sheets provides the native EPOCHTODATE function, simplifying timestamp conversion into a single, clean formula.

Syntax and Units in the EPOCHTODATE Function

The EPOCHTODATE function accepts raw numeric epoch values and converts them directly into Google Sheets datetime serial numbers:

=EPOCHTODATE(timestamp, [unit])

• timestamp (required): The positive integer or cell reference containing the Unix epoch timestamp.
• unit (optional): An integer specifying the time resolution of your timestamp:
  • 1: Seconds (the standard default used by Linux, MySQL, and Unix systems, typically 10 digits).
  • 2: Milliseconds (used by JavaScript, MongoDB, and AWS logs, typically 13 digits).
  • 3: Microseconds (used by high-frequency financial telemetry, typically 16 digits).
  • 4: Nanoseconds (used by Go and low-level kernel metrics, typically 19 digits).

Converting Unix Timestamps in Seconds

Suppose cell A2 contains the 10-digit Unix timestamp 1728562800:

1. In cell B2, enter the following formula:

   =EPOCHTODATE(A2, 1)

   (You can omit the second argument if your source data uses seconds, as 1 is default).
2. Google Sheets returns the underlying date serial number (e.g. 45575.5).
3. To display the result as a readable calendar date and time, highlight the cell and click Format > Number > Date time in the top menu.
4. The cell will display as 10/10/2024 12:20:00 UTC.

Handling Millisecond and Microsecond Timestamps

When ingesting server logs generated by JavaScript or Python applications, timestamps are frequently recorded in milliseconds (13 digits):

1. If cell A2 contains 1728562800000, trying to parse it as seconds will produce year errors well past the 2038 boundary.
2. Supply unit code 2 to instruct Google Sheets to evaluate milliseconds:

   =EPOCHTODATE(A2, 2)

3. For microsecond-level telemetry (16 digits), supply unit code 3:

   =EPOCHTODATE(A2, 3)

Converting Entire Columns Using ARRAYFORMULA

To convert an entire column of imported log timestamps without dragging formulas manually down thousands of rows, wrap EPOCHTODATE inside an ARRAYFORMULA with a blank-cell check:

=ARRAYFORMULA(IF(ISBLANK(A2:A), "", EPOCHTODATE(A2:A, 1)))

Place this single formula into cell B2; it will automatically spill calculated date values down the entire sheet, adjusting dynamically as new log entries are appended.

Converting Dates Back to Epoch Timestamps

If you need to perform the reverse operation—converting a standard human calendar date in cell B2 back into a Unix epoch integer for database ingestion—Google Sheets provides the companion function DATEVALUE or mathematical subtraction:

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

This returns the precise number of seconds elapsed since the 1970 epoch, ready for API payloads and export scripts.