Identify a leap year in Excel: We all know what a leap year is. Every 4 years (almost) we get a February 29th. The ‘almost’ is the key thing. There are some conditions (none of which most of us will see in our lifetimes) where a leap year may get skipped.
Here’s the algorithm for the determination that I found on Wikipedia. You can also read up on the history and such of the leap year there as well if you like.
If the Year is divisible by 400, it IS a leap year. Otherwise if the year is divisible by 100, it’s NOT a leap year. If none of the above, and the year is divisible by 4, it IS a leap year. Otherwise it is just NOT a leap year.How to identify a leap year in Excel
And, here’s the Excel formula to identify whether a given year is a leap year. This formula will return “TRUE” if the year is a leap year and “FALSE” otherwise. Note that “year” below is the value, the cell or the formula that has the year value you want to test.
=IF(MOD(year,400)=0,TRUE,IF(MOD(year,100)=0,FALSE,IF(MOD(year,4)=0,TRUE,FALSE)))
If you have an entire date you’re using instead of just a year, just use the YEAR function to pull the year out of the date value you have in Excel.
=YEAR(yourdatevalue)
With this formula, you can identify a leap year in Excel.
There you go.
