Calculation Issues

Why Excel Accepts February 29, 1900 as a Valid Date

Partner Editor Oct 10, 2026 870 views

Question details

The user wants to understand why Excel recognizes February 29, 1900, as a valid date despite 1900 not being a leap year.

Why Excel Accepts February 29, 1900 as a Valid Date

Product: Microsoft Excel
Device & OS: not provided
Scenario: Working with historical dates in Excel and noticing an incorrect leap year calculation for the year 1900.
Observed behavior: Excel treats February 29, 1900, as a valid date, affecting serial numbers and functions like WEEKDAY for dates before March 1, 1900.

Before you start

Keep in mind that this behavior is an intentional design choice for legacy compatibility and cannot be disabled or changed within the software settings.

Solution 1Recommended

Understanding the Lotus 1-2-3 Compatibility History

Learn why this historical date discrepancy exists and why Microsoft chose to preserve it.

Lotus 1-2-3, an early popular spreadsheet program, incorrectly treated 1900 as a leap year. When Microsoft released Multiplan and later Excel, they retained this exact serial-date system to ensure complete compatibility with existing Lotus files.

Correcting this issue now would shift all date serial numbers after February 28, 1900, by one day. This would break millions of existing workbooks, alter historical date logic, and disrupt functions like WEEKDAY across the world.

Identify affected dates

Recognize that this issue only affects dates prior to March 1, 1900. Dates from March 1, 1900, onwards are calculated correctly in relation to one another.

Check other century years

Rest assured that Excel handles other non-leap century years correctly, such as 2100, 2200, and 2300. Only 1900 exhibits this specific legacy behavior.

Understanding the Lotus 1-2-3 Compatibility History

Impact on Date Calculations: If you are calculating date differences spanning across February 29, 1900, you will need to manually subtract one day to get the correct real-world difference.

Free Microsoft Office alternative

Use WPS Spreadsheet for Highly Compatible Data Management

WPS Office offers a lightweight, completely free alternative to Microsoft Office. It maintains precise compatibility with Excel's date systems and historical calculations, ensuring your legacy workbooks transition seamlessly without breaking any formulas.

1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite.
2. Open WPS Spreadsheet: Launch WPS Spreadsheet, which serves as the equivalent to Microsoft Excel.
3. Open Your Legacy Workbook: Open your existing Excel files to see that all date calculations, including historical dates, are preserved flawlessly.

Free, lightweight, and fast-loading alternative to Microsoft Office.100% compatibility with Microsoft Excel (.xlsx, .xls) files and legacy date serial systems.Familiar user interface requiring zero learning curve for Excel users.

microsoft office alternative - wps office

Frequently Asked Questions

Can I fix or turn off the 1900 leap year bug in Excel?

No, this is a hardcoded behavior maintained strictly for backward compatibility. Correcting it would break date calculations and serial numbers in almost all existing spreadsheets.

Does this affect leap years in other centuries like 2000 or 2100?

No. The year 2000 was a real leap year, and Excel calculates future non-leap century years (like 2100 or 2200) correctly. The discrepancy is strictly isolated to the year 1900.

How do I calculate precise date differences before March 1, 1900?

If your mathematical calculations span across February 29, 1900, you must manually subtract one day from the total difference to account for the phantom leap day.

Excel & Data Problems