Use Vintage Calendars

Step 6 Determine Whether a Given Year was a Leap Year

In cell D2, we're going to enter a fairly complex formula to figure out whether the year is a leap year or not. Since the equation is fairly complex, I'll show it below and then explain how it does what it does. Here's the equation (enter it exactly as shown in cell D2):

=IF(OR(MOD(YEAR(A2),400)=0,AND(MOD(YEAR(A2),4)=0,MOD(YEAR(A2),100)<>0)),"Leap", "")

MOD is short for modulus, which is an integer that can be divided without remainder into the difference between two other integers. For example, 2 is a modulus of 5 and 9. The OR in the equation tests for three conditions, two of which (the AND) must occur together:

The year in cell A2, when divided by 400, is whole number (i.e. the remainder =0)

Here's the AND (both of the following conditions must occur):
The year in cell A2, when divided by 4, is whole number (i.e. the remainder =0)
The year in cell A2, when divided by 100, is not a whole number (i.e. the remainder <>0)

If the first of the three conditions is met OR both of the next two conditions are met, then the formula returns the word Leap. Otherwise, the formula results in a blank (that's the ""). When you press enter, you won't see anything because 1901 wasn't a leap year.