Both topics are discussed in more detail below.
In the formula, the year is extracted with theYEAR function.
The answer depends on a subtle behavior in Excel’s date system.
In other words, the DATE function rolls the date forward to the next valid date.
If the result is TRUE, we have a leap year.
If the result is FALSE (the month is 3), the year is not a leap year.
- directly to the DATE function.
The first limitation is easy to work around, as explained in the next section.
The second limitation is more fundamental and requires a different approach.
Excel’s 1900 problem
Excel erroneously treats 1900 as a leap year.
This is a simple way to avoid classifying 1900 as a leap year.
However, before we look at a solution, I need to introduce the Julian and Gregorian calendars.
The Julian Calendar was a system of dates instituted by Julius Caesar in 46 BC.
The idea was to account for an extra day every four years, creating a “leap year”.
However, it turns out this calculation is not correct.
As a result, the Julian Calendar was over-correcting by about 8 days every 1000 years.
Further study in the 16th century resulted in a better solution.
The idea was that centenary years would not be leap yearsunless they were divisible by 400.
This meant three out of four centenary yearswould notbe leap years.
In other words, every 400 years there would be 97 leap years instead of 100 leap years.
The long-form formula below is meant to follow this logic.
To handle years before 1900, we need a math-based formula that doesn’t require Excel’s date functions.
YEAR Function
The Excel YEAR function returns the year component of a date as a 4-digit number.
For example, MOD(10,3) = 1.
The result of MOD carries the same sign as the divisor.
AND returns TRUEonly if all the conditions are met.
If any conditions are not met, the AND function returns FALSE.
OR returns TRUEif any condition is TRUE.
If all conditions are FALSE, the OR function returns FALSE.
The OR function is often used with the IF function and can be combined…
