Go Back

How to Check if a Year is a Leap Year in Excel

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.

Example 1:

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. 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

Having problem with this syntax statement. Syntax doesn't error, but I get a false response when any month is not FEBRUARY. =IF(INPUTSHEET!C1="FEBRUARY",IF(INPUTSHEET!C4="Y","29","")) I'm trying to get "29" in any month that isn't February and only regardless of Leap Year, the "Y" in INPUTSHEET!C4 is = Leap Year.
Solved by T. Q. in 26 mins
Our fiscal year always starts July 1st and ends June 30th of the following year, our sales week starts Tuesday, and ends the following Monday. I'm looking for a formula that will calculate each date of the full year (taking into account a leap year) and not go beyond June 30th of the following year. Additionally, I already have this formula to figure our the first Monday: =IF(WEEKDAY($A2)=2, $A2, IF(WEEKDAY($A2)=1, $A2+2, $A2+9-WEEKDAY($A2))) but I'd like to figure out how to incorporate having it not go beyond June 30th.
Solved by O. H. in 25 mins
Here is one Excel problem that I have been stuck on: Ensure each year level is a 2 digit number(i.e. starts with a zero) A130 Year 7.A Maths - Washington A131 Year 7.B Maths - Jefferson A132 Year 8.A Maths - Adams A133 Year 8.B Maths - Washington A134 Year 9.A Maths - Adams A135 Year 9.B Maths - Washington A136 Year 10.A Maths - Washington A137 Year 10.B Maths - Adams A138 Year 10.C Maths - Washington A139 Year 11 Maths - Washington A140 Year 12.A Maths - Jefferson A141 Year 12.B Maths - Adams A142 Year 13 Maths - Adams Expected Output: Year07.A Maths- Washington My solution(which does not work) was trying to use =IF(VALUE(A130), TEXT(A130, "#00')) VALUE(A130) finds the number from the cell and TEXT(A130, "#00") converts a whole number into a decimal number(two digits). And I combined those two formulas together, but it gave me an error message "#VALUE!" Is there any better way of solving this kind of problem? Hope to hear back from you!
Solved by D. L. in 14 mins

Leave a Comment

avatar