Convert spreadsheet dates to Unix timestamps (and Unix epoch seconds back to dates)

Anchor on DATE(1970, 1, 1) = serial 25569

Unix time counts seconds elapsed since 1970-01-01 00:00:00 UTC. In the 1900 spreadsheet date system, DATE(1970, 1, 1) is serial 25569 and each day contains 86,400 seconds.

Subtracting DATE(1970, 1, 1) from any UTC date-time serial and multiplying by 86400 converts to Unix seconds; dividing Unix seconds by 86400 and adding DATE(1970, 1, 1) converts API timestamps back into spreadsheet dates!

A spreadsheet serial has no timezone metadata. The input must already represent UTC; this arithmetic does not convert local time to UTC or resolve daylight-saving offsets.

Convert UTC date to Unix seconds and reverse

Wrapping in ROUND(..., 0) prevents fractional-second floating-point drift when timestamps include hours and minutes.

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

Convert 13-digit JavaScript millisecond epochs

If an API exports 13-digit milliseconds (like 1767225600000), divide by 86400000 instead of 86400 before adding DATE(1970, 1, 1).

=YEAR((B2 / 86400) + DATE(1970, 1, 1))

Further reading

• Microsoft Support — DATE function