Leap Year in Excel (2024)

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:

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.

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.

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.

YEAR(A2)&":"&YEAR(B2) combines the years to make a text string "2000:2005".
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.
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 formulaso 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:

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:

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 complexas 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.