Unix Timestamps and Conversion in Google Sheets

August 7th, 2026

APIs, logs, and many databases store time as a Unix timestamp: seconds (or milliseconds) since 1970-01-01 00:00:00 UTC, also called the epoch. Spreadsheets prefer calendar dates. Google Sheets bridges the two with EPOCHTODATE in one direction and simple date arithmetic in the other. This guide shows both conversions, the seconds-vs-milliseconds trap, and the timezone caveats that trip people up.

What a Unix timestamp is

| Value | Meaning (UTC) | | --- | --- | | 0 | 1970-01-01 00:00:00 | | 1599811200 | 2020-09-11 00:00:00 (seconds) | | 1599811200000 | Same instant (milliseconds) |

Sheets stores dates as serial numbers (days since a spreadsheet epoch), which is why you convert rather than “format” a raw Unix number as a date.

EPOCHTODATE — Unix → Sheets datetime

EPOCHTODATE converts an epoch value to a datetime in UTC.

=EPOCHTODATE(1599811200)
=EPOCHTODATE(1599811200000, "milliseconds")
=EPOCHTODATE(A2, "seconds")

The optional second argument is the unit: "seconds" (default), "milliseconds", or "microseconds". If your source column has 13-digit numbers, you almost certainly want "milliseconds".

After conversion, format the cell as Date time (Format → Number) so you see calendar values instead of serials.

Date → Unix (seconds)

Sheets has no single built-in “DATETOEPOCH,” but the math is straightforward. One day has 86400 seconds:

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

If A2 is a pure date at midnight interpreted as UTC, that returns the classic Unix seconds value. For a datetime that includes a time of day:

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

still works, because the fractional part of the date serial is the fraction of a day — multiplying by 86400 turns it into seconds.

Round or truncate when you need an integer token for an API:

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

Milliseconds instead of seconds

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

or, from an existing seconds value:

=A2 * 1000

Seconds vs milliseconds (the classic bug)

| Digits (typical) | Unit | What to do | | --- | --- | --- | | ~10 | seconds | EPOCHTODATE(A2) | | ~13 | milliseconds | EPOCHTODATE(A2, "milliseconds") | | ~16 | microseconds | EPOCHTODATE(A2, "microseconds") |

Symptoms of getting it wrong:

• Seconds treated as milliseconds → date near 1970
• Milliseconds treated as seconds → year far in the future

Quick heuristic:

=IF(A2>1E12, EPOCHTODATE(A2, "milliseconds"), EPOCHTODATE(A2))

Use only when a single column mixes conventions; prefer a fixed unit per column when you control the pipeline.

Timezone caveats (brief but important)

• EPOCHTODATE is UTC. The serial it returns represents that UTC instant. Display depends on how you format the cell and on spreadsheet locale; collaborators in other zones may read a different local clock time if you mentally treat the value as local.
• Spreadsheet “date only” cells are midnights, not “full days in every zone.” (date - DATE(1970,1,1))*86400 assumes the date serial you see is the instant you intend — usually midnight in the sheet’s conceptual calendar, which may not match “midnight local in Tokyo.”
• NOW() and TODAY() follow the spreadsheet’s timezone settings. Do not mix “UTC epoch from an API” with NOW() in the same SLA calculation without being explicit about zones.
• For civil-calendar work that is not epoch-based, stick to DATE, TIME, and the patterns in Working with Dates in Google Sheets: A Comprehensive Guide. For text that only looks like a date, see Normalize Dates in Google Sheets with DATEVALUE and TEXT.

Practical patterns

API import column → readable date

=ARRAYFORMULA(IF(A2:A="",, EPOCHTODATE(A2:A, "milliseconds")))

Age of an event in hours

=(NOW() - EPOCHTODATE(A2, "seconds")) * 24

Interpret with the timezone caveat above; for pure UTC duration between two epoch seconds values, subtract the numbers and divide by 3600 instead.

Round-trip check

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

If B2 was a UTC midnight date, you should land back on the same calendar day.

Common mistakes

• Forgetting the unit on millisecond data. Always pass "milliseconds" when values are in the 1e12 range.
• Formatting a raw Unix number as a Date. That treats the number as a day serial, not as epoch seconds — you will get nonsense centuries away.
• Assuming local time equals UTC. Epoch is defined in UTC; document which side of the conversion owns the zone.
• Using TEXT before converting. Convert with EPOCHTODATE first, then format for display with TEXT if needed:

=TEXT(EPOCHTODATE(A2), "yyyy-mm-dd hh:mm")

Which approach should you pick?

| Goal | Approach | | --- | --- | | Unix → Sheets date/time | EPOCHTODATE | | Sheets date → Unix seconds | (date - DATE(1970,1,1))*86400 | | Sheets date → Unix ms | same × 1000 | | Mixed unit column | digit-length heuristic or clean at import |

Going further

For broader date arithmetic (workdays, month ends, durations), continue with Working with Dates in Google Sheets: A Comprehensive Guide. Function reference: EPOCHTODATE, DATE, NOW, TEXT.