How to Fix the February 1900 Leap Year Bug in Excel
Question details
The user needs to understand and work around the historical 1900 leap year bug in Excel that causes inconsistencies in date formulas, day shifts, and perpetual calendars.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating historical calendars or performing date arithmetic involving dates in January and February of the year 1900.
- Observed behavior
- Excel incorrectly treats 1900 as a leap year, causing February 29, 1900, to appear in some templates, while functions like EOMONTH return February 28, 1900. Days of the week for early 1900 are also shifted inaccurately.
Before applying formula workarounds, confirm whether your spreadsheet genuinely requires accurate historical dates from the year 1900; if your data only involves dates after March 1, 1900, this historical bug will not affect your calculations.
Use Dedicated Formula Branches for the Year 1900
Create specific formula logic to adjust day shifts and bypass leap year calculations when the year is exactly 1900.
Spreadsheet software intentionally kept the legacy Lotus 1-2-3 bug that assumed 1900 was a leap year to ensure backward compatibility. Because of this, January 1 to February 28, 1900, are assigned serial numbers 1 to 59, and an invalid February 29 is assigned 60. To build an accurate perpetual calendar for 1900, you must manually offset these dates using formula conditions.
Locate the cell containing your core date formula or calendar starting point (for example, cell A1).
Wrap your standard date arithmetic in an IF function checking the year. Type =IF(YEAR(A1)=1900, [1900 logic], [standard logic]) into your formula bar.
If the date falls in January or February 1900, subtract 1 from your weekday calculation result. This corrects the day shift, ensuring January 1, 1900, is accurately identified as a Monday rather than a Sunday.
Instead of adding days, use the formula =EOMONTH(DATE(1900,2,1),0). This function correctly evaluates that February 1900 has 28 days, preventing February 29 from rendering in standard calendar grids.

Hide Invalid Dates using Conditional Formatting
Prevent formatting errors in calendar templates by adding a specific conditional formatting rule to hide February 29, 1900.
Handle Complex Date Formulas with WPS Spreadsheet
WPS Spreadsheet offers robust compatibility with standard Excel date functions, allowing you to seamlessly open, edit, and apply formula workarounds for historical date calculations without losing data integrity.
- 1. Download WPS Office: Download and install WPS Office for free from the official website.
- 2. Open your spreadsheet: Open your existing calendar template or Excel file containing the date formulas in WPS Spreadsheet.
- 3. Apply formula workarounds: Select the target date cells and apply your custom IF formulas or conditional formatting rules to handle the 1900 date exceptions.
- 4. Save your document: Save your file in .xlsx format; your complex conditional logic will remain perfectly intact.

Frequently Asked Questions
Why does Excel think 1900 is a leap year?
Excel inherited this bug from Lotus 1-2-3, which was the dominant spreadsheet software when Excel was first released. Microsoft intentionally kept the bug to ensure that spreadsheets imported from Lotus 1-2-3 would calculate date serial numbers exactly the same way without corrupting historical data.
How does the 1900 bug affect the day of the week?
Because the system inserts a non-existent February 29, 1900 (assigning it serial number 60), the serial numbers for January 1 through February 28, 1900, are shifted by one day. For example, standard functions report January 1, 1900, as a Sunday, when historically it was a Monday.
Does the EOMONTH function calculate February 1900 correctly?
Yes, interestingly, the formula =EOMONTH(DATE(1900,2,1),0) correctly identifies that February 1900 has 28 days and returns February 28. However, standard serial number addition (e.g., manually adding 1 to February 28, 1900) will still yield February 29, 1900.
Will this date bug ever be fixed in standard spreadsheet software?
No. Fixing it now would break millions of existing spreadsheets globally that rely on the current serial number system for date arithmetic and macro execution. It is considered a permanent legacy behavior across standard spreadsheet applications.




