Earth takes about 365.2422 days to go around the Sun (the tropical year). A calendar of exactly 365 days would drift by almost a quarter of a day every year — about a month every 120 years, so seasons would slowly move through the calendar.
In this article
The Julian fix: every 4 years
Julius Caesar’s calendar (46 BC) added one day every four years, making the average year 365.25 days. Close — but 0.0078 days too long, which adds up to about one day every 128 years.
The Gregorian fix: skip three leap days every 400 years
By the 16th century the calendar had drifted about ten days. Pope Gregory XIII’s reform in 1582 removed ten days and changed the rule:
- A year divisible by 4 is a leap year…
- …except years divisible by 100…
- …unless they are also divisible by 400.
So 2000 was a leap year, but 1900 was not and 2100 will not be. The average year becomes 365.2425 days — off by only about one day in 3,000 years.
The Excel twist
Excel treats 1900 as a leap year and has a 29 February 1900 — a bug copied deliberately from Lotus 1-2-3 for compatibility. It only affects dates before March 1900.
=IF(OR(MOD(A2,400)=0,AND(MOD(A2,4)=0,MOD(A2,100)<>0)),"Leap year","Not a leap year")More calendar and date tricks: Excel date formulas cheat sheet.