Excel timestamp converter

Convert a Unix timestamp to an Excel serial date and back, with the exact formula Excel uses to do it internally.

A Unix timestamp and an Excel date are both just numbers, but they count from different starting points and in different units, so a raw 1786795200 pasted into a spreadsheet cell reads as gibberish instead of a date. Unix time counts whole seconds since January 1, 1970. Excel counts whole days — with fractions of a day for the time of day — since a date it calls serial number 1, which is January 1, 1900. Neither system converts to the other automatically, and Excel has no built-in function named for the job, which is why "unix timestamp excel" is one of the most common searches from anyone who has ever received an API export, a server log, or a database dump with a raw epoch column and needs it readable in a spreadsheet. The fix is two lines of date arithmetic, not a plugin or an add-in: once you know the single constant that bridges the two epochs, both directions are one formula each, and they work in Excel, Google Sheets, and LibreOffice Calc identically. Every formula below is copy-ready as printed: each one reads its input from cell A1, so paste it into an empty cell, change A1 to wherever your own value actually sits, and nothing else needs editing.

Why Excel's epoch is what it is

Excel's date system is a holdover from Lotus 1-2-3, which treated 1900 as a leap year even though it was not — a bug that stuck once spreadsheets full of dates depended on it. Serial number 1 is January 1, 1900; serial number 60 is the nonexistent February 29, 1900; every date from March 1900 onward is one day ahead of what a true Julian day count would give you. You never need to correct for this by hand, because the offset used below — 25569 — was measured against Excel's actual behavior, bug included, not against a theoretical calendar. It is exactly what DATE(1970,1,1) evaluates to when you type it into any modern version of Excel.

Unix timestamp to Excel date

With a Unix timestamp in seconds sitting in cell A1:

=A1/86400+DATE(1970,1,1)

Format the resulting cell as a date or datetime and it displays correctly. The formula divides the timestamp by the number of seconds in a day to get a day-fraction count, then adds the serial number for the Unix epoch so the result lands on Excel's own calendar.

Excel date to Unix timestamp

With a date in cell A1, the inverse formula:

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

Format the result as a plain number rather than a date — Excel otherwise tries to reinterpret a large integer as a far-future date, which is confusing but harmless and fixed by changing the cell's number format.

Working with milliseconds

Some APIs — JavaScript's Date.now() among them — hand out timestamps in milliseconds rather than seconds. Swap 86400 for 86400000 in both directions: 

=A1/86400000+DATE(1970,1,1)          ' milliseconds to date
=(A1-DATE(1970,1,1))*86400000        ' date to milliseconds

A ten-digit value is almost certainly seconds and a thirteen-digit value is almost certainly milliseconds — count the digits before you pick a formula. If you are pasting a whole column of mixed-unit values, the unit detector sorts that out per row.

Google Sheets has one thing Excel does not: EPOCHTODATE(), which does the division and the epoch shift for you. Its optional unit argument is where the same trap lives — 1 is seconds and is the default, 2 is milliseconds, 3 is microseconds — so a millisecond column handed to a bare =EPOCHTODATE(A1) is read as seconds and lands roughly fifty thousand years in the future, silently, exactly as the arithmetic formulas above do. Two more things Google documents and it is worth knowing before a report goes out: the result is UTC rather than the spreadsheet's own timezone, and negative timestamps are rejected outright, so a pre-1970 date needs the date-arithmetic form instead. 

=EPOCHTODATE(A1)      ' unit omitted = seconds — a 13-digit value lands in the year 50000+
=EPOCHTODATE(A1, 1)   ' 1 = seconds      (the same thing, said out loud)
=EPOCHTODATE(A1, 2)   ' 2 = milliseconds (what a 13-digit column needs)
=EPOCHTODATE(A1, 3)   ' 3 = microseconds (16 digits)

The date-arithmetic formulas on this page work unchanged in Google Sheets as well, so EPOCHTODATE() is a convenience rather than a requirement — and the one to reach for when a sheet has to stay readable to somebody who will not open a formula bar.

Formatting the result as text

Wrap either conversion in TEXT() to get a plain string instead of a date serial:

=TEXT(A1/86400+DATE(1970,1,1), "yyyy-mm-dd hh:mm:ss")

That is the form you want before exporting to CSV or concatenating a timestamp into a label, since a formatted date cell still holds a number underneath and will round-trip back to a serial number the moment it leaves Excel.

What Excel's CONVERT() function actually does

It is a reasonable guess that a function literally named CONVERT() would handle this, but Excel's CONVERT() converts between physical measurement units — temperature, distance, weight, volume — and has no unit pair for Unix time or Excel serial dates. =CONVERT(A1,"unix","excel") is not a real formula; the date-arithmetic formulas above are the actual mechanism, and every spreadsheet that claims to do epoch conversion "with CONVERT" is really just doing the same division and addition under a different name.

Frequently asked questions

What is the Excel epoch?

Excel's date system starts at serial number 1 for January 1, 1900. A bug inherited from Lotus 1-2-3 makes Excel treat 1900 as a leap year, which it was not, so every date from March 1900 onward is shifted by one day relative to a true day count. The bug is baked into every version of Excel, so it is not something you need to correct for — formulas built on it already match what Excel itself displays.

How do I convert a Unix timestamp to a date in Excel?

With a Unix timestamp in seconds in cell A1, use =A1/86400+DATE(1970,1,1) and format the cell as a date or datetime. Excel evaluates DATE(1970,1,1) to serial number 25569, which is the offset between Excel's epoch and the Unix epoch.

How do I convert an Excel date back to a Unix timestamp?

With a date in cell A1, use =(A1-DATE(1970,1,1))*86400 and format the result as a number. This is the exact inverse of the timestamp-to-date formula.

Does Excel's CONVERT() function convert Unix timestamps?

No. Excel's built-in CONVERT() function converts between measurement units, such as temperature, distance, and weight — it has no unit pair for Unix time or Excel serial dates. The conversion has to be done with the date-arithmetic formulas above, not CONVERT().

How do I handle timestamps that are in milliseconds?

Divide by 86400000 instead of 86400: =A1/86400000+DATE(1970,1,1). Ten-digit timestamps are seconds and thirteen-digit timestamps are milliseconds — check the digit count first if you are not sure which one you have.

How do I display the converted date as readable text?

Wrap the conversion in TEXT(), for example =TEXT(A1/86400+DATE(1970,1,1),"yyyy-mm-dd hh:mm:ss"). This returns a text string rather than a date serial, which is useful for exporting to CSV or concatenating into a label.

Where to go next

If you have a whole column of raw timestamps to convert at once rather than one formula at a time, the bulk converter pastes in a column and copies one back out. If a value is showing up as 1970 instead of the date you expected, the 1970 debugger names the bug and gives you the fix. And when the epoch column is still in a database rather than a sheet, it is usually cheaper to convert it in the query with FROM_UNIXTIME() or to_timestamp() and export it already readable.