Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Clear out junk files and repair common Windows errors3Scan for outdated or missing drivers - takes under a minuteFor a Unix timestamp in seconds in A1, use =A1/86400+25569 in a workbook using Excel’s 1900 date system. For milliseconds, use =A1/86400000+25569. Format the result as a date and time. These formulas interpret the timestamp as UTC; convert it explicitly if you need a different time zone.
Convert a Unix timestamp to an Excel date
A Unix timestamp is a count of seconds or milliseconds from 1970-01-01 00:00:00 UTC. Excel stores dates as serial numbers and times as fractions of a day, so the formula depends on the timestamp’s unit.
As an Amazon Associate I earn from qualifying purchases.
| Input in A1 | Formula for a 1900-system workbook |
|---|---|
| Unix timestamp in seconds | =A1/86400+25569 |
| Unix timestamp in milliseconds | =A1/86400000+25569 |
Divide by the number of seconds in a day—or milliseconds in a day—to turn the timestamp into a day fraction. Adding 25,569 aligns the Unix epoch with Excel’s 1900-system date serials. The offset follows from the documented date-system bases and calendar arithmetic; Microsoft’s support pages document the date systems, not these specific Unix conversion formulas.
- Enter the numeric timestamp in
A1and identify whether it is in seconds or milliseconds. - Enter the matching formula in another cell.
- Format that cell as a date/time so Excel displays the serial’s whole-day and fractional-day parts.
For a known UTC input, check the displayed result against its expected UTC date and time. A numeric timestamp identifies an instant; Excel does not infer your local time zone. If you need local wall-clock time, apply a deliberate time-zone conversion separately.
Why Excel serial 60 is not a real date
In Excel’s 1900 date system, serial 1 is January 1, 1900. Excel also treats 1900 as if it had been a leap year, preserving a historical compatibility error. That makes serial 60 correspond to the fictional February 29, 1900. The neighboring serials are 59 for February 28 and 61 for March 1.
This is an Excel compatibility artifact, not a real Gregorian date, a Unix timestamp, or a time-zone effect. Microsoft says the assumption originated in Lotus 1-2-3 and was retained so worksheets could move between programs using compatible serial dates. Correcting it would shift almost all existing worksheet dates by a day, change results from functions such as WEEKDAY, and disrupt compatibility. Microsoft documents the remaining effect this way: “The WEEKDAY function returns incorrect values for dates before March 1, 1900.” (Microsoft Learn, last updated March 30, 2026.)
Do not use serial 60 as a valid Gregorian date in calculations or exports. Its special behavior matters near the beginning of Excel’s 1900 date system, not as a correction to ordinary modern Unix timestamps.
Check whether the workbook uses the 1900 or 1904 date system
Excel supports two date systems. The same calendar date has serials that differ by 1,462 days between them, so applying a formula for the wrong system produces a large date shift.
Rank #3
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
| Date system | First date and serial | Unix-seconds formula |
|---|---|---|
| 1900 | January 1, 1900 — serial 1 | =A1/86400+25569 |
| 1904 | January 1, 1904 — serial 0 | =A1/86400+24107 |
For milliseconds in a 1904-system workbook, use =A1/86400000+24107. Alternatively, calculate the 1900-system result and subtract 1,462. Microsoft’s example for July 5, 2011, gives serial 40,729 in the 1900 system and 39,267 in the 1904 system (Microsoft Support). The 1904 system’s serial-zero date is specified in Microsoft’s [MS-XLS] Date1904 documentation.
Confirm the workbook’s date-system setting before converting data, especially when moving values between workbooks. The setting affects how serial numbers are interpreted; a serial alone does not identify its date system.
Quick Recap
Best Value
Common conversion errors to avoid
- Using the wrong timestamp unit: dividing milliseconds by 86,400 instead of 86,400,000 makes the result far too large.
- Using the wrong date system: a 1900-system formula in a 1904-system workbook shifts the interpreted date by 1,462 days.
- Confusing UTC with local time: the formula converts the timestamp’s UTC instant; it does not apply a location’s time zone or daylight-saving rules.
- Using
DATEVALUEon a number:DATEVALUEconverts recognized date text to an Excel serial; it is not the operation for a numeric Unix timestamp. Microsoft notes that text parsing can depend on system settings, omitted years use the computer’s current year, and time information in the text is ignored (Microsoft Support). - Expecting arbitrary pre-1900 timestamps to display normally: dates before the supported starting range of the 1900 system need extra care; ordinary Excel date formatting is not a guarantee of display for every earlier date.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
