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: 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: 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
