Unix timestamps in Google Sheets
Google Sheets has a built-in function for Unix time: EPOCHTODATE. Going the other way still takes a small formula.
Get the current Unix timestamp
# NOW() uses the spreadsheet's timezone
=ROUND((NOW() - DATE(1970,1,1)) * 86400, 0)
Convert a Unix timestamp to a date
# seconds
=EPOCHTODATE(A2)
# milliseconds
=EPOCHTODATE(A2, 2)
# microseconds
=EPOCHTODATE(A2, 3)
The result is in UTC, not the spreadsheet's timezone.
Convert a date to a Unix timestamp
# A2 holds a UTC date-time
=(A2 - DATE(1970,1,1)) * 86400
Timezones
# show UTC as IST (UTC+5:30)
=EPOCHTODATE(A2) + 5.5/24
The spreadsheet timezone is set in File → Settings. It affects NOW() and TODAY(), not EPOCHTODATE.
How Sheets stores dates
Sheets stores a date-time as the number of days since December 30, 1899, with the time of day as the fraction of a day. The number carries no timezone offset; the spreadsheet's timezone setting decides how it is read. A Unix timestamp counts seconds from 1970-01-01, so the formulas above subtract DATE(1970,1,1) (serial number 25569) and multiply by 86,400 seconds per day.
Common mistakes
- EPOCHTODATE returns UTC while NOW() returns spreadsheet time, so mixing them in one formula shifts by the zone offset.
- Fixed offsets don't follow daylight saving changes. Use Apps Script for zone-aware conversion.
Complete example
A1: 1700000000
B1: =EPOCHTODATE(A1) → 2023-11-14 22:13:20
C1: =(B1 - DATE(1970,1,1)) * 86400 → 1700000000
Check any value with the epoch converter, or convert a whole column with the batch converter. All examples use 1700000000 (2023-11-14 22:13:20 UTC) and 1794650400 (2026-11-14 10:00:00 UTC).