iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
For a Unix timestamp in seconds in cell A1, use =A1/86400+25569 in a workbook using Excel’s 1900 date system. For milliseconds, use =A1/86400000+25569. Format the result cell as a date and time. Treat the input as UTC: Excel won’t infer a time zone from a numeric timestamp.
Convert a Unix timestamp to an Excel date
These formulas convert a numeric Unix timestamp—the number of seconds or milliseconds since 1970-01-01 00:00:00 UTC—into an Excel serial date for a workbook using the 1900 date system. The offset 25,569 aligns the Unix epoch with that system’s serial dates; dividing by the number of seconds in a day turns the timestamp into days and a fractional day.
| Input in A1 | Formula for a 1900-system workbook |
|---|---|
| Unix timestamp in seconds | =A1/86400+25569 |
| Unix timestamp in milliseconds | =A1/86400000+25569 |
- Enter the numeric Unix timestamp in
A1. - Enter the matching formula in another cell. Use the seconds formula only for seconds and the milliseconds formula only for milliseconds.
- Format the result cell as a date/time. Excel stores the date as a serial number and the time as a fraction of a day; the format controls how those values appear.
For a 1904-system workbook, subtract 1,462 from the result of the corresponding formula, or use =A1/86400+24107 for seconds. Confirm the workbook’s date system before choosing an offset. Microsoft’s date-systems explanation gives the 1,462-day difference and examples; the [MS-XLS] Date1904 specification defines serial 0 as January 1, 1904.
Use UTC or explicitly convert to a local time zone
A Unix timestamp identifies an instant; it does not contain a local time zone. The formulas above therefore give the timestamp’s UTC date and time. If you need a local wall-clock time, apply a deliberate time-zone conversion separately, accounting for the target zone’s daylight-saving rules where relevant. Simply changing the cell’s date/time format does not convert time zones.
Why Excel treats serial 60 as February 29, 1900
In the 1900 date system, Excel assigns serial 1 to January 1, 1900, and preserves a historical leap-year mistake: it treats 1900 as if it had a February 29. Consequently, serial 60 displays as February 29, 1900, even though that date did not exist in the Gregorian calendar. The adjacent serials are 59 for February 28, 1900, and 61 for March 1, 1900.
This is an Excel compatibility artifact, not a real Unix date and not a time-zone problem. Microsoft says the behavior originated with Lotus 1-2-3 and was retained to keep worksheet serial dates compatible. Correcting it would shift almost all current worksheet dates by a day, change results from functions such as WEEKDAY, and disrupt compatibility. Microsoft’s Excel troubleshooting documentation, last updated March 30, 2026, states: “The WEEKDAY function returns incorrect values for dates before March 1, 1900.” Avoid treating serial 60 as a valid Gregorian date in calculations or exports.
Rank #2
- Used Book in Good Condition
Check the workbook’s date system
Excel supports both the 1900 and 1904 date systems. The same calendar date has serial values that differ by 1,462 days between them, so applying a formula for the wrong system can produce a result displaced by four years and one day. Microsoft’s example for July 5, 2011 is serial 40729 in the 1900 system and 39267 in the 1904 system; these are Microsoft’s documented examples, not a general conversion test.
Free tools Windows power users keep installed
One-click scans. No signup required.
| 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 |
Microsoft documents the 1900 and 1904 systems and the 1,462-day offset on its Excel date-systems page. The starting serials are specified in Microsoft Learn’s [MS-XLS] Date1904 documentation, last updated October 15, 2020.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Avoid common conversion errors
- Using the wrong timestamp unit: a milliseconds value needs 86,400,000 as the divisor; using 86,400 produces a result on the wrong scale.
- Using the wrong date system: verify whether the workbook uses 1900 or 1904 before selecting the offset.
- Confusing formatting with conversion: a date/time format reveals Excel’s stored serial and fractional day; it does not change the underlying time zone.
- Using DATEVALUE on a numeric timestamp:
DATEVALUEconverts recognized date text, not Unix seconds or milliseconds. 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. See Microsoft’s DATEVALUE documentation. - Assuming dates before 1900 will display normally: Excel’s 1900 date system has a supported starting range. Do not assume ordinary date formatting will display arbitrary pre-1900 Unix timestamps as calendar dates.
For real data, check the timestamp unit and workbook date system, then compare the formula result with a known UTC timestamp. If the value is used for an export or calendar calculation, ensure the result represents a real calendar date rather than Excel’s serial-60 compatibility exception.
Quick Recap
Best Value
- 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
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.
