How to Check for a Leap Year in Excel

Test whether a year is a leap year with DATE, count the days in any year, and apply the full 4/100/400 rule with MOD.

You will learn to

• Test for a leap year using February’s last day
• Count the days in a year
• Apply the Gregorian leap-year rule with MOD

How it works

The simplest leap-year test asks Excel how long February is: =DAY(DATE(A2,3,0))=29. DATE with day 0 of March returns the last day of February, which is the 29th only in leap years. Count the days in a year with =DATE(A2,12,31)-DATE(A2,1,0).

The rule itself: a year is a leap year if it is divisible by 4, except century years, which must also be divisible by 400. In a formula: =OR(MOD(A2,400)=0,AND(MOD(A2,4)=0,MOD(A2,100)<>0)). So 2000 was a leap year but 2100 will not be.

=DAY(DATE(A2,3,0))=29