Hi - Dave here.
Happy Friday!
How can you test if a year is a leap year?
If you are testing any year after 1900, you can do it with a formula like this:
=MONTH(DATE(YEAR(date),2,29))=2
This is the formula in cell C5 of the worksheet below.
[
Download the workbook and read the full explanation]
Of course, the devil is in the details. The clever formula above works great for years after 1900, but it will fail in 1900 and earlier for two reasons: (1) Excel incorrectly thinks 1900 is a leap year and (2) Excel's date system can only handle dates beginning with January 1, 1900. The article above provides a more robust formula for testing earlier years, and a brief explanation of how the logic is derived from the Gregorian calendar system we use today. Download the workbook and try it out yourself.
These formulas work in all versions of Excel.
Excel formulas
We maintain a list of over 500 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
