Exceljet

Quick, clean, and to the point

Year is a leap year

Excel formula: Year is a leap year
Generic formula 
=MONTH(DATE(YEAR(date),2,29))=2
Summary 

To test whether the year of a certain date is a leap year, you can use a formula that uses the MONTH, YEAR, and DATE functions. In the example shown, the formula in cell C5 is:

=MONTH(DATE(YEAR(B5),2,29))=2
Explanation 

The core of this formula is the DATE function, which will automatically adjust to month and year values that are out of range. In the formula, year is passed into date unchanged, along with 2 for month (February) and 29 for the day. In leap years, February has 29 days, so the DATE function will simply return the date February 29 of the year.

In non-leap years however, DATE will return the date March 1 of the year, because there is no 29th day in February, and DATE simply rolls the date forward to the next month.

Finally, the MONTH function simply extracts the month from the result provided by DATE, which is compared to 2 using the equal sign. If the month is 2, the formula returns TRUE. If not, the month must be 3 and the formula returns FALSE.

Test year only

To check a year only, instead of a full date, instead of a date, modify the formula as below:

=MONTH(DATE(year,2,29))=2

In this version, we don't extract a year value from a date, we pass a year value (i.e. 2020)  directly to the DATE function.

A more literal solution

If the formula above seems too clever, and you want a more literal solution, the formula below will test if a year contains 366 days instead:

=DATE(YEAR(date)+1,1,1)-DATE(YEAR(date),1,1)=366

This formula generates two dates based on the date provided: (1) the first of the next year, and (2) the first of the current year. Then the first of the current year is subtracted from the first of the next year. In non-leap years, the result is 365 and the formula returns FALSE. In leap years, the result is 366 and the formula returns TRUE.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.