Computer Tutoring Logo
Est. 2002
Menu Button

Leap Year in Excel

Share

How to work out the leap year in Excel?

Working out the leap year in Excel is a handy formula to know. This formula can be used for:

  • Date Calculations - Accurately calculating the difference between two dates.
  • Scheduling - For events that are dependent on the number of days in a year.
  • Financial Analysis - Financial modeling and analysis where calculations are on an annual basis. Or to give accurate interest calculations in financial models.
  • Payroll - for correctly calculating salaries.

This tutorial shows how to create a formula in Excel to check if a year is a leap year or not. You will then go on to see how to work out how many leap year are between two dates. Finally, you will see how to calculate a how many leap years are between two dates no matter if they are century leap years or not.

Leap Year in Excel - Basic Formula

To work out if a date is a leap year in Excel you can use the formula:

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

You can then wrap the formula in an Excel IF function.

=IF(MONTH(DATE(YEAR(A2),2,29))=2,"Leap Year","")

The cell will display "Leap Year" if the year of the date in cell A2 is a leap year. So as to better understand, try out the following:

Start a blank Excel workbook with a list of dates:

Leap Year in Excel - Dummy Data

In cell B1 enter the text Leap Year?

Now in cell B2 enter the formula: =IF(MONTH(DATE(YEAR(A2),2,29))=2,"Leap Year","").

Auto fill the formula down to see the leap year formula working.

Excel Leap Year Formula Result

Count number of leap years between dates

To count the number of leap years between two dates in Excel you can use the formula:

=SUMPRODUCT(
IF(
MOD(ROW(INDIRECT(YEAR(A2)&":"&YEAR(B2))),4)=0,
1,0)
)

  • YEAR(A2) and YEAR(B2): This extracts the year from the dates in cells A2 and B2.
  • INDIRECT(YEAR(A2)&":"&YEAR(B2)): This creates a reference to a range of years from the year in cell A2 to the year in cell B2.
  • ROW(INDIRECT(YEAR(A2)&":"&YEAR(B2))): This returns an array of all the years between the year in cell A2 and the year in cell B2.
  • MOD(ROW(INDIRECT(YEAR(A2)&":"&YEAR(B2))),4): This calculates the remainder when each year in the array is divided by 4. In a leap year, the year number is evenly divisible by 4, so the MOD function will return 0 for leap years.
  • IF(
    MOD(ROW(INDIRECT(YEAR(A2)&":"&YEAR(B2))),4)=0
    ,1,0):
    This IF function returns 1 if the year is a leap year (remainder is 0 when divided by 4) and 0 otherwise.
  • SUMPRODUCT(
    IF(MOD(
    ROW( INDIRECT(YEAR(A2)&":"&YEAR(B2))),4)=0,1,0
    )
    )

    Finally, the SUMPRODUCT function sums up all the 1s and 0s returned by the IF function, effectively counting the number of leap years between the two dates in cells A2 and B2.

In summary, this formula counts the number of leap years between two dates by checking each year in the range to see if it is evenly divisible by 4 (a basic check for a leap year). Note that this formula does not account for the exception to the leap year rule: years that are divisible by 100 are not leap years unless they are also divisible by 400.

Try this:

Add a new sheet to the Excel workbook.

Enter the data:

Count leap years in Excel - Starting file

In Cell C2 enter the formula:

=SUMPRODUCT(
IF(
MOD(ROW(INDIRECT(YEAR(A2)&":"&YEAR(B2))),4)=0
,1,0)
)

Your results should look like:

Count number of leap years between dates

Why do I need ROW(INDIRECT( in the formula?

Let's simplify the explanation for the ROW(INDIRECT(YEAR(A2)&":"&YEAR(B2))) part of the formula:

Imagine you have the year 2000 in cell A2 and the year 2005 in cell B2.

  1. YEAR(A2)&":"&YEAR(B2) combines the years to make a text string "2000:2005".
  2. INDIRECT("2000:2005") turns that text string into an actual reference, like a range of cells or numbers. In this case, it's treating it as a range of numbers from 2000 to 2005.
  3. ROW(INDIRECT("2000:2005")) then turns that range into an array of individual numbers: 2000, 2001, 2002, 2003, 2004, 2005.

So, this part of the formula is creating a list of years from the year in cell A2 to the year in cell B2.

Leap years on a centennial year in Excel

However this isn't the complete picture. You see a century year is only counted as a leap year if that year is divisible by 400. (Check out the Royal Museums Greenwich website to find out more).

This means the above calculation is out by 2 leap years. There should be 54 leap years between the 1st January 1900 and 26th September 2123. So we need to adjust the formula so that the Excel calculation checks to see if the centennial year is divisible by 400 or not.

=SUMPRODUCT(
IF(
(MOD(ROW(INDIRECT(YEAR(A2)&":"&YEAR(B2))),4)=0) *
(MOD(ROW(INDIRECT(YEAR(A2)&":"&YEAR(B2))),100)<>0) +
(MOD(ROW(INDIRECT(YEAR(A2)&":"&YEAR(B2))),400)=0),
1,0))

If you simply want the calculation to work, simply enter the above formula into cell D2 of the Excel file you've been working on. Oh and don't forget to add a heading:

Correct count of leap years between dates

Counting leap years formula explanation:

Let's break down the given Excel formula into simpler terms. This formula is used to count the number of leap years between two dates, considering all the leap year rules (divisible by 4, not divisible by 100 unless also divisible by 400).

Here's a step-by-step breakdown:

  1. Dates in Cells: You have two dates in cells A2 and B2.
  2. Year Extraction: The YEAR(A2) and YEAR(B2) parts extract the years from these dates.
  3. Creating a List of Years: ROW(INDIRECT(YEAR(A2)&":"&YEAR(B2))) creates a list of all years between the years extracted from A2 and B2.
  4. Checking Divisibility by 4: (MOD(ROW(...),4)=0) checks if the years in the list are divisible by 4.
  5. Checking Divisibility by 100: (MOD(ROW(...),100)<>0) checks if the years in the list are NOT divisible by 100.
  6. Checking Divisibility by 400: (MOD(ROW(...),400)=0) checks if the years in the list are divisible by 400.
  7. Combining Checks: The formula combines the above checks to identify leap years. A year is a leap year if it is divisible by 4 AND either not divisible by 100 OR divisible by 400.
  8. Counting Leap Years: SUMPRODUCT(IF(...,1,0)) counts the number of years in the list that meet the leap year criteria.

In simpler terms:

  • The formula looks at each year between two dates you provide.
  • It checks each year to see if it's a leap year based on the leap year rules.
  • It then counts up all the years that are leap years and gives you the total number.

Why use the "*" and the "+" operators?

In the context of the given formula, the * (asterisk) and + (plus) signs are used as logical operators to combine different conditions. Let's break down their roles:

The * (Asterisk) as AND Operator:

In the part:

(MOD(ROW(INDIRECT(YEAR(A2)&":"&YEAR(B2))),4)=0) *
(MOD(ROW(INDIRECT(YEAR(A2)&":"&YEAR(B2))),100)<>0)

  • The * is used as an AND operator.
  • It checks two conditions:
    1. If the year is divisible by 4 (MOD(...,4)=0).
    2. If the year is not divisible by 100 (MOD(...,100)<>0).
  • Both conditions must be true for the result to be true (1). If either condition is false, the result is false (0).

The + (Plus) as OR Operator:

In the part:

+ (MOD(ROW(INDIRECT(YEAR(A2)&":"&YEAR(B2))),400)=0)

  • The + is used as an OR operator.
  • It checks an additional condition: if the year is divisible by 400 (MOD(...,400)=0).
  • This condition being true (1) will make the entire expression true, regardless of the other conditions.

In Summary:

  • The * is checking if the year is divisible by 4 AND not divisible by 100.
  • The + then checks if the year is divisible by 400.
  • The year is considered a leap year if it meets the first two conditions OR the third condition.

This combination of conditions accounts for the leap year rules: a year is a leap year if it is divisible by 4, not divisible by 100 unless it is also divisible by 400.

Finding the Number of Leap Years Between Two Dates

The above formula works if you're not bothered about the exact dates of the leap years. Meaning that you want to count how many years count as a leap year between the two dates. But what if you wanted to be a little more specific. Say, for example, you wanting to see how many 29th's of February's there are between two dates. This would make the formula a little more complex as you can see:

=SUMPRODUCT(
(MOD(ROW(INDIRECT(YEAR(A2) & ":" & YEAR(B2))), 4) = 0) *
(YEAR(A2) <= ROW(INDIRECT(YEAR(A2) & ":" & YEAR(B2)))) *
(ROW(INDIRECT(YEAR(A2) & ":" & YEAR(B2))) <= YEAR(B2)) *
(DATE(ROW(INDIRECT(YEAR(A2) & ":" & YEAR(B2))), 2, 29) >= A2) *
(DATE(ROW(INDIRECT(YEAR(A2) & ":" & YEAR(B2))), 2, 29) <= B2)
)

Expanation of the above leap year between two dates.

Generating Year Array:

ROW(INDIRECT(YEAR(A2) & ":" & YEAR(B2)))

  • YEAR(A2) and YEAR(B2): These functions extract the year part from the dates in A2 and B2.
  • INDIRECT(YEAR(A2) & ":" & YEAR(B2)): This creates a reference to a range that starts from the year in A2 and ends at the year in B2. For example, if A2 is "30th April 2020" and B2 is "29th February 2024", it generates the reference 2020:2024.
  • ROW(...): This function returns an array of numbers representing the row numbers for the range specified, which, in this case, are just the years from 2020 to 2024. Hence, it outputs an array like [2020, 2021, 2022, 2023, 2024].

Leap Year Check:

MOD(ROW(...), 4) = 0

  • This checks if each year in the array is divisible by 4, which is a basic test for identifying leap years. This condition returns TRUE for potential leap years and FALSE otherwise.

Year Range Validation:

(YEAR(A2) <= ROW(...)) * (ROW(...) <= YEAR(B2))

  • These two conditions check if the years generated are within the range of years between A2 and B2. They ensure that the years considered are not outside the specified date range.

Date Comparisons:

(DATE(ROW(...), 2, 29) >= A2) * (DATE(ROW(...), 2, 29) <= B2)

  • DATE(ROW(...), 2, 29): This function constructs the date for February 29 of each year in the generated array. If the year is not a leap year, Excel handles this by rolling over to March 1st.
  • These conditions check if February 29 of each year (where applicable) falls within the date range specified in A2 and B2. These ensure that only the leap years that actually include a February 29 within the date range are counted.

Counting Valid Leap Years:

SUMPRODUCT(...)

  • The SUMPRODUCT function multiplies the arrays resulting from each condition and sums up the products. Since each condition is an array of TRUE (1) or FALSE (0) values, the multiplication acts as an AND operator (as explained in my previous response). The formula only counts (sums) a 1 if all conditions are TRUE for a given year, which means it was a leap year with February 29 falling within the date range.

Overall Functionality

In essence, this formula is calculating the number of years between A2 and B2 that are leap years (divisible by 4) and include February 29 in that range. It ensures accuracy by verifying both the leap year condition and the actual inclusion of February 29 within the specified limits of A2 and B2. This is a comprehensive approach to counting such specific leap years using Excel's array and logical functionalities.

I reckon you would appreciate the completed file, cell D2 has the correct formula.

Excel Leap Year Summary

Hopefully the above makes sense and you can see why understanding how calculating the leap year in Excel is a useful formula to know.

Interested in booking an Excel course for you and your team to build your confidence in using Excel? Then check out some of our Excel courses:

We also offer a range of database and analysis courses using Power BI.

Other Excel Videos Tutorials


Other Excel Accounts Tutorials




At Computer Tutoring, we prioritise your privacy and only collect the necessary information to provide our services. We will only share the personal data you provide in a form if you have opted in for those services. Our website also uses Google Analytics. You have the right to access, amend, or request deletion of your personal data by contacting us at info@computertutoring.co.uk.

For more details, please read our full Privacy Policy.