When working with dates in Excel, you might need to distinguish whether or not a year is a leap year. This article gives you the tools to determine whether or not a certain year meets that criteria.
Check if a year is a leap year in Excel
The YEAR, MONTH and DATE functions in Excel can be used to check whether a particular year on a certain date is a leap one. The procedure for doing this is demonstrated below.
To check if the date in a certain year is a leap year or not using the MONTH, DATE and YEAR functions, follow the procedure below:
Click on Formulas on the menu bar in Excel.
- Next, click on Date and Time.
- Scroll down the list that displays and click on MONTH.
The dialogue box will display as shown below:
Figure 1: How to access the MONTH function in Excel
By now, you should have already typed in the worksheet the various dates which you wish to check if they are leap years. The moment the MONTH dialogue box opens you have already started the operation on the dates typed in the worksheet.
- In the Serial number field, type the following:
DATE(YEAR(B8),2,29)=2.B8 is the address of the cell in the worksheet which contains the particular date you want to check if it is in a leap year.
- Click on OK.
It will show you the result displayed in the screenshot below:
Figure 2: Result displayed by the MONTH, DATE and YEAR functions
Interpretation of the Result
What is displayed above is a worksheet having two columns labeled Date and Leap year. The column on the left contains various dates which are to be checked if they fall in a leap year. The column on the right contains the result. When the particular date falls in a leap year, the formula returns TRUE as the result. However, where it does not fall in a leap year, the formula returns False.
Another way to check whether the year is a leap year is to simply type the formula in C8,
=MONTH(DATE(YEAR(B8),2,29))=2 into the cell on the right of the row you are interested in. Hit the enter key when you finish typing, and the result will show. Note that B8 is the address of the cell and should be modified accordingly.
However, whether you use the first or second method above, you will have to apply it to each date individually.
The way the formula functions
The basic working of the formula is through the DATE function. The 2 in the formula represents the month of February which varies depending on whether the year is a leap year or not. In a leap year, there are 29 days in February. This is the essence of the 29 in the formula. If the year is a leap year, the DATE function will mark February 29.
However, if it is not a leap year, the extra day will simply roll over from February into March 1. The MONTH function just focuses on the month component of the date. If it is a leap year, the month will be February and 2 will be there, but if it is not a leap year, 3 will be there representing March.
If the value is 2, the function will return True in the final result, but if the value is 3, the function will return False.
Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.