Check if a year is a leap year (4 / 100 / 400 Gregorian rule)

Why dividing by 4 alone fails on century years

Under the Gregorian calendar, a year is a leap year if it is divisible by 4, EXCEPT century years divisible by 100 are NOT leap years unless they are also divisible by 400. Thus 2000 and 2024 are leap years (366 days), whereas 1900 and 2100 are common years (365 days).

Combining OR(MOD(yr, 400)=0, AND(MOD(yr, 4)=0, MOD(yr, 100)<>0)) implements the Gregorian divisibility rule for integer years.

Exact Gregorian leap-year test and days-in-year

If your input cell holds a full date instead of a 4-digit year, replace A2 with YEAR(A2).

=OR(MOD(A2, 400)=0, AND(MOD(A2, 4)=0, MOD(A2, 100)<>0))

Return the Gregorian number of days in the year

For years after 1900, MONTH(DATE(A2, 2, 29)) = 2 returns TRUE when February has 29 days and FALSE when it rolls into March 1.

=IF(OR(MOD(A2, 400)=0, AND(MOD(A2, 4)=0, MOD(A2, 100)<>0)), 366, 365)

Inputs and interpretation

Use a positive integer year, not a date serial or fractional value. The arithmetic rule describes the proleptic Gregorian calendar, not local historical calendar adoption. For the DATE rollover alternative use years 1901–9999: Excel adjusts years 0–1899 by adding 1900 and retains its 1900 leap-day anomaly.

Further reading

• Microsoft Support — Method to determine whether a year is a leap year