Unix Timestamp in Excel and Google Sheets
A Unix timestamp converts to a spreadsheet date with =(A1/86400)+DATE(1970,1,1). Divide by 86400 because a day holds that many seconds, then anchor the result at 1 January 1970. For a 13-digit millisecond value divide by 86400000 instead. Format the cell as a date and time to see the result.
By Michael Lip · Updated 2026-10-28
Spreadsheets store a date as a serial number of days. Unix time counts seconds from 1 January 1970. One division and one anchor turn that second count into a date the cell can format.
The formula for a seconds timestamp
=(A1/86400)+DATE(1970,1,1)
Put the timestamp in cell A1. Divide it by 86400, the number of seconds in a day. Add DATE(1970,1,1), the anchor date. Set the cell format to a date and time, for example yyyy-mm-dd hh:mm:ss.
Test it with a known value. Unix time 1700000000 is 2023-11-14T22:13:20Z. The division gives 19675.925925925927 days. Adding the anchor gives 2023-11-14 22:13:20.
The formula for a milliseconds timestamp
=(A1/86400000)+DATE(1970,1,1)
JavaScript and Java usually hand you milliseconds. A millisecond timestamp has 13 digits and a seconds timestamp has 10. Divide by 86400000, the number of milliseconds in a day. The rest of the formula does not change.
Convert a date back to a timestamp
=(A1-DATE(1970,1,1))*86400
This formula is the inverse. It subtracts the anchor and multiplies by 86400. Wrap it in ROUND when the cell holds a fractional second.
Why DATE(1970,1,1) and not 25569
Do not copy the number 25569 from a blog post. That number is the serial for 1 January 1970 in the 1900 date system only. Excel also offers a 1904 date system for some Mac workbooks, and the serial changes with it. DATE(1970,1,1) builds the anchor from the workbook's own system, so the formula survives the switch.
The same formulas in Google Sheets
Google Sheets uses the same serial day number. Paste the formulas without a change. Both DATE and the arithmetic behave the same way. If a cell shows a five-digit number, apply Format, Number, Date and time.
Common faults
- A 10-digit value divided by 86400000 gives a date in 1970. Count the digits before you divide.
- A text value does not divide. Use
VALUE(A1)or import the column as a number. - A negative timestamp is valid. It gives a date before 1970 and the formula still works.
- The timestamp holds no time zone. Unix time is UTC, so the formula gives a UTC wall clock. Add your offset when the sheet must show local time.
Check the answer before you fill a column
Run one value through the epoch to date converter and compare. Use the milliseconds converter for a 13-digit value. To go the other way, use the date to epoch converter. For the full list of storage formats, read the timestamp format encyclopedia.
Frequently Asked Questions
What formula converts a Unix timestamp in Excel?
=(A1/86400)+DATE(1970,1,1). Divide the seconds by 86400 and add the date 1 January 1970.
Why do I get a date in 1970 when I use my timestamp?
The value is probably milliseconds and you divided by 86400. Divide a 13-digit value by 86400000.
Do Excel and Google Sheets use the same formula?
Yes. Both use a serial day number, so the same formula works in both.