Go Back

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

Figure 1. Year is a Leap Year in Excel.

We are going to use a formula that consists of the Excel MONTH, YEAR, and DATE functions in order to ascertain whether the year of a given date is a leap year,

Generic Formula

=MONTH(DATE(YEAR(date),2,29))=2

In the formula above, February has 29 days during a leap year, and the number 2 is for the month of February.

How to use the Excel MONTH, YEAR, and DATE Functions.

We are now going to utilize the Excel MONTH YEAR and DATE Functions in 3 simple steps;

  1. Enter the day, month, and year values in a column of our worksheet.

Be sure to label a separate column for Excel to indicate which of the dates occur on a leap year.

See example illustrated below;

Figure 2. Year is a Leap Year in Excel.

  1. In the example illustrated above, we are going to enter the following MONTH, YEAR, and DATE formula into cell B2;

=MONTH(DATE(YEAR(A2),2,29))=2

Figure 3. Year is a  Leap Year in Excel.

  1. After Excel has returned the initial result, copy the formula into the cells down the column for the next results to be returned.

Figure 4. Year is a Leap Year in Excel.

If the year is a Leap Year, the formula returns TRUE and if not, the formula returns FALSE.

However, during non-leap years, the date March 1 of the year will be returned by the DATE Function because the 29th day does not exist.

Instant Connection to an Expert through our Excelchat Service:

Our live Excelchat Service is here for you. We have Excel Experts available 24/7 to answer any Excel questions you may have. Guaranteed connection within 30 seconds and a customized solution for you within 20 minutes

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