Computers do not store dates. They store counts — a number of units elapsed since some arbitrary starting moment — and remarkably, almost every major system picked a different starting moment. This page covers the main ones, why each was chosen, and the bugs those decisions left behind, including a leap year that never existed and a satellite clock that has already reset twice.
A timestamp is not self-describing
The number 1785700000 means nothing on its own. Read as Unix seconds it is 2026. Read as Windows FILETIME it is the year 1601 plus a few minutes. Every timestamp needs an epoch and a unit before it means anything, and assuming the wrong one is a reliable way to be wrong by centuries.
The epochs in use
| System | Epoch | Unit | Rolls over |
|---|---|---|---|
| Unix / POSIX | 1 Jan 1970 | Seconds | 2038 at 32-bit |
| Windows FILETIME | 1 Jan 1601 | 100 nanoseconds | Year 30828 |
| Excel (default) | 0 Jan 1900 | Days | Year 9999 |
| Excel (legacy Mac) | 1 Jan 1904 | Days | Year 9999 |
| Classic Mac OS | 1 Jan 1904 | Seconds | 2040 at 32-bit |
| NTP | 1 Jan 1900 | Seconds | 2036 |
| GPS | 6 Jan 1980 | Weeks + seconds | Every 19.7 years |
| .NET DateTime | 1 Jan 0001 | 100 nanoseconds | Year 9999 |
| VMS | 17 Nov 1858 | 100 nanoseconds | Year 31086 |
| Cocoa / Apple | 1 Jan 2001 | Seconds | Far future |
Ten systems, nine different zero dates spanning 143 years. Each was a reasonable local decision, and collectively they guarantee that moving date data between systems requires care.
Windows and the 400-year cycle
1601 looks arbitrary and is the most mathematically thoughtful choice on the list.
The Gregorian leap year rule has three parts: divisible by 4 is a leap year, unless divisible by 100, unless also divisible by 400. That pattern repeats exactly every 400 years — the same sequence of leap and common years, and even the same days of the week.
Starting at 1601 places the epoch at the beginning of a cycle. Date arithmetic can then work in whole 400-year units and handle the remainder with a fixed lookup, with no special cases at the boundaries. It also comfortably predates any file creation date a computer would need to store.
The unit is 100-nanosecond intervals, which sounds excessive and gives a 64-bit counter a range to the year 30828 at nanosecond-scale precision. When the alternative is running out of range, over-provisioning costs nothing.
The leap year Excel invented
This is the most entertaining bug in the history of date handling, and it is still shipping.
1900 was not a leap year. It is divisible by 100 and not by 400, so the Gregorian rule skips it. There was no 29 February 1900.
Lotus 1-2-3, the dominant spreadsheet of the 1980s, got this wrong and treated 1900 as a leap year. When Microsoft built Excel, it needed to import Lotus files and produce identical results — so it deliberately reproduced the bug.
Microsoft has documented this openly for decades and will not fix it, for a defensible reason: correcting it would shift every date serial in every existing spreadsheet by one day. Millions of financial models would silently produce different numbers. Keeping a known, documented bug is safer than a correction that breaks working files.
⚠️ The knock-on effect
Because Excel counts a day that does not exist, its serial numbers are one higher than they should be for everything from 1 March 1900 onwards. Any conversion between Excel serials and real dates must account for this:
Dates before 1 March 1900 need an additional day of correction, and dates before 1900 cannot be represented at all.
And Excel had two epochs
Excel for Macintosh historically defaulted to a 1904 date system, inherited from classic Mac OS, while Excel for Windows used 1900. The setting still exists per workbook.
The failure mode is quiet and nasty: open a 1904-based workbook in a 1900-based Excel and every date shifts by 1,462 days — four years and one day. Nothing errors. The dates simply become wrong, uniformly, by four years, and a financial model continues to calculate confidently.
Why Macs chose 1904
Classic Mac OS counted seconds from 1 January 1904, and the reason is a genuine engineering simplification.
1904 was a leap year. Starting there means the first year of the count is a leap year, and with a 32-bit unsigned counter the representable range ends in 2040 — comfortably before 2100, the next year when the divisible-by-100 exception applies.
Within that window, the leap year rule is simply "every four years", with no exceptions at all. On a 1984 machine with an 8 MHz processor and 128KB of memory, eliminating that branch from every date calculation was worth the trade of being unable to represent dates before 1904.
GPS and the rollover that keeps happening
GPS is the only system here whose epoch problem has already caused real-world failures, twice.
GPS time counts from 6 January 1980 — the first Sunday after the system became operational — as a week number plus seconds into the week. The original signal allocates just 10 bits to the week number.
At each rollover the week counter returns to zero. Receivers that assume week zero means 1980 report a date nearly twenty years in the past — and both rollovers produced documented failures in navigation equipment, timing systems and network infrastructure that had not been updated.
Modernised GPS signals use a 13-bit week number, extending the cycle to about 157 years. But receivers in the field can be decades old, and equipment installed today may still be running when the 2038 rollover arrives.
One more GPS peculiarity worth knowing: GPS time does not observe leap seconds. UTC has added leap seconds since 1980, so GPS time now runs some seconds ahead of UTC, and the offset is broadcast in the navigation message. Software that treats GPS time as UTC is quietly wrong by that offset.
NTP and 2036
Network Time Protocol — the system that keeps essentially every networked clock correct — counts seconds from 1 January 1900 in a 32-bit unsigned field.
This arrives two years before the more famous 2038 Unix problem and receives a fraction of the attention. The protocol handles it with an era number, so a correctly implemented client knows which 136-year period it is in — but that requires the implementation to be correct, and a device with no other time source has no way to determine its era independently.
NTP is also the layer that most systems get their time from at boot. A failure there propagates everywhere at once.
Converting between epochs
Every conversion needs two adjustments: an offset for the different zero dates, and a scale for the different units.
| From → To Unix seconds | Formula |
|---|---|
| Windows FILETIME | ft / 10000000 - 11644473600 |
| Excel serial (1900) | (n - 25569) * 86400 |
| Excel serial (1904) | (n - 24107) * 86400 |
| Classic Mac seconds | n - 2082844800 |
| NTP seconds | n - 2208988800 |
| Cocoa seconds | n + 978307200 |
| .NET ticks | ticks / 10000000 - 62135596800 |
| JavaScript ms | ms / 1000 |
✅ Sanity-check every conversion
Epoch mistakes produce dates that are wildly wrong rather than subtly wrong, which is genuinely helpful — the errors are easy to spot if you look. A validation range catches nearly all of them:
What the pattern shows
Look across these choices and a consistent shape appears. Each epoch was picked to make one specific thing easier at the time — a leap year cycle boundary, a compatible import, a branch removed from a hot loop, a satellite launch date. Every one of those was locally reasonable.
What none of them anticipated was how long the decision would last. The 1984 Macintosh engineer choosing 1904 was not planning for 2040. The GPS designers allocating 10 bits in the 1970s were solving a real bandwidth constraint. Lotus's leap year bug outlived Lotus by four decades and is still shipping in the world's most-used spreadsheet.
The practical lesson for anything being built now: use 64-bit integers, count in UTC seconds from the Unix epoch, and record the epoch explicitly in any format you define. The cost of over-provisioning a timestamp is a few bytes. The cost of under-provisioning one is a rollover that arrives long after everyone who understood the decision has moved on.
Converting a timestamp right now?
Paste a Unix timestamp and get a readable date in any timezone, or convert back — instantly, in your browser.
Open the Timestamp Converter →Summary
- A timestamp needs an epoch and a unit to mean anything.
- Windows counts from 1601 because it starts a 400-year Gregorian cycle.
- Excel counts a 29 February 1900 that never existed, deliberately, for Lotus compatibility.
- Excel had two epochs — a 1904 workbook opened in 1900 mode shifts every date by 1,462 days.
- Classic Macs used 1904 to eliminate the century leap year rule entirely.
- GPS has already rolled over twice, in 1999 and 2019, and will again around 2038.
- NTP rolls over in 2036 — two years before the better-known 2038 problem.
- Validate converted dates against a plausible range. Epoch errors are large and easy to catch.
Frequently Asked Questions
Why does Windows count time from 1601?
1601 is the first year of a 400-year Gregorian calendar cycle. Because the leap year pattern repeats exactly every 400 years, starting at a cycle boundary makes date arithmetic simpler and avoids special cases. It also comfortably predates any date a computer file would plausibly need to represent.
Is it true Excel thinks 1900 was a leap year?
Yes, deliberately. 1900 was not a leap year — years divisible by 100 are skipped unless also divisible by 400 — but Lotus 1-2-3 had the bug, and Microsoft replicated it so spreadsheets would import correctly. It is still in Excel today, which means every date before 1 March 1900 is off by one day.
Why did classic Macs count from 1904?
Because 1904 is a leap year, and starting on one lets you handle the leap cycle with a simple four-year repeat rather than special-casing the century rule. It was a genuine simplification for the limited hardware of the era, at the cost of being unable to represent earlier dates at all.
What is the GPS week rollover?
The original GPS signal carries a 10-bit week number, which counts to 1023 and then resets to zero — every 1,024 weeks, or roughly 19.7 years. It has already happened twice, in August 1999 and April 2019, and receivers that did not handle it reported dates two decades in the past.
Does the choice of epoch still matter?
Yes, whenever data crosses systems. A timestamp is meaningless without knowing which epoch and which unit it uses, and converting between them is a common source of dates that are wrong by decades or centuries. Every conversion needs both an offset and a scale factor.