Convert a Unix Timestamp to an Excel Date and Serial Number

The One Formula, and the Number Inside It

To convert a Unix timestamp to a date in Excel you divide and then add. The Unix timestamp Excel formula everyone copies is =(A1/86400)+25569, and it does two separate jobs that are worth separating in your head. Epoch time is the same thing under another name, so to convert epoch time to a date in Excel you use this identical expression.

• Divide by 86,400. Epoch time counts seconds; Excel counts days. There are 86,400 seconds in a day, so this rescales the unit.
• Add 25,569. The two systems start in different centuries. Excel serial 1 is 1 January 1900; epoch second 0 is 1 January 1970. Exactly 25,569 days separate them, so this rebases the origin.

That is the whole conversion. You can sanity-check it without a calculator: put 0 in the formula and you get 25569, and formatting that cell as a date shows 1 January 1970. If your version of the formula fails that test, the constant has been mistyped.

Ten Digits, Thirteen, or Sixteen

Nothing inside the number says which unit it is in, so the only clue is how big it is. Systems that log in milliseconds are common — JavaScript, Java and most JSON APIs — and microsecond values turn up in database exports.

Getting this wrong is loud rather than subtle. To convert milliseconds to a date in Excel with the seconds divisor throws the result roughly a thousand times too far into the future, well past the year 9999 where Excel gives up entirely. The converter above reports which unit it settled on, and lets you overrule it when a low-magnitude millisecond value looks like seconds.

Why You Get a Number Instead of a Date

The commonest follow-up question is not about the maths at all. The formula returns 45244.925925926 and the cell shows exactly that, which looks like failure and is not.

An Excel date serial number is the date. The whole part counts days, the fraction counts the time of day — 0.5 is midday, 0.75 is six in the evening — and a cell format is the only thing that renders it as calendar text. Select the cell, press Ctrl+1, and pick a date format, or write a custom one such as yyyy-mm-dd hh:mm:ss. The underlying number is untouched, which is why you can still subtract two of them to get an interval in days.

If you want the text rather than a formatted number, wrap it: =TEXT((A1/86400)+25569,"yyyy-mm-dd hh:mm:ss"). That produces a string, so remember it will no longer sort or subtract as a date.

The Leap Day That Never Happened

The Excel 1900 leap year bug is real, deliberate, and permanent. 1900 was not a leap year — century years need to divide by 400, and 1900 does not — but Lotus 1-2-3 thought otherwise, and Excel copied the mistake to keep spreadsheets interchangeable in the 1980s. Microsoft has documented it and will not fix it, because doing so would silently move every date in every workbook ever saved.

The practical shape of it:

• Serial 59 is 28 February 1900, which is correct.
• Serial 60 is 29 February 1900, a day that did not exist.
• Serial 61 is 1 March 1900, correct again — and everything after it is correct.

So the damage is confined to the first sixty days of 1900. Anything derived from a Unix timestamp starts in 1970 and lands far clear of it. It only bites when you push a large negative timestamp through the formula, and the converter warns you when a result strays into that window. It is also why date arithmetic spanning that boundary can be off by one, and why Excel and databases disagree on ancient dates.

The 1904 System, or Why Everything Is Four Years Out

Excel supports a second origin. The Excel 1904 date system for Mac was the default on Macintosh versions for years, chosen precisely to dodge the 1900 problem, and it counts from 1 January 1904 instead. The correct constant becomes 24107.

The gap between the two is 1,462 days: four calendar years plus the leap day of 1904. A workbook set to one system, opened and edited by someone assuming the other, shifts every date by four years and one day — enough to look like a data error and not enough to look like a bug.

Check it under File → Options → Advanced → When calculating this workbook. Do not flip the setting to fix a mismatch: it re-interprets the serials already stored rather than converting them, so all your existing dates move. Fix the constant in the formula instead, and set the toggle in the converter above to match the workbook you are pasting into.

Serials Are Naive, and That Is Your Problem to Solve

A Unix timestamp is unambiguous: it counts seconds since a fixed instant in UTC. An Excel serial is not. It records a wall-clock reading with nothing attached — no offset field, no zone name, no daylight-saving flag. Excel will never ask which one you meant, and never warn you that two columns disagree.

That leaves a decision to make before you paste anything into a sheet. Storing UTC keeps the column consistent for anyone who later exports it, and is the right default for logs and audit trails. Storing local wall clock reads naturally for a report that a human will look at once. Either is defensible; mixing them inside one column is not.

The toggle above applies the shift explicitly and shows both readings side by side, along with the zone your browser is in, so you can see what the difference actually is before you commit. Whichever you choose, put it in the column header. Daylight saving means a fixed offset added by hand will be wrong for half the year.

The Power Query Version

A formula in a column is fine for a one-off. For a query that refreshes, the Power Query Unix timestamp conversion is cleaner, because it produces a real datetime rather than a number that needs formatting afterwards:

= #datetime(1970, 1, 1, 0, 0, 0)
    + #duration(0, 0, 0, [Timestamp])

For milliseconds, divide the column by 1000 first. Neither constant appears, which is rather the point — there is no 25569 to mistype and no date system to get wrong, because M works in real datetimes throughout. The result arrives in the sheet already typed as a date.

Before 1970, and Other Edges

Negative timestamps are legal and describe dates before the epoch. They convert with the same formula and produce serials below 25569, which Excel handles happily right down to serial 1. Below that the number goes negative, no date format can render it, and you get hashes or #NUM!.

Two more edges worth knowing. Excel stores about 15 significant digits, so a microsecond timestamp loses sub-second precision on the way through — convert to milliseconds first if that fraction matters. And a timestamp arriving as text rather than a number returns #VALUE!; multiply by 1 or run Text to Columns to coerce it before converting.