How to determine year is a leap year in Excel
If you want 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.
Formula
=MONTH(DATE(YEAR(date),2,29))=2
Explanation
In the example shown, the formula in cell C5 is:
=MONTH(DATE(YEAR(B5),2,29))=2
How the formula works
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 and DATE simply rolls the date forward into the next month.
Finally, MONTH simply extracts the month from the result provided by DATE, which is compared to 2 using the equal sign.
If month is 2, the formula returns TRUE. If not, the month must be 3 and the formula returns FALSE.
Just want to test a year?
If you just want to check if a year (i.e. 2015, 2016, etc.), modify the formula as follows:
=MONTH(DATE(year,2,29))=2
A more literal solution
If the formula above seems to “clever” and you want a more literal solution, you can test if a year contains 366 days instead with this formula:
=DATE(YEAR(date)+1,1,1)-DATE(YEAR(date),1,1)
This formula generates two dates, then subtracts January 1st of the given year from January 1st of the next year.