How to calculate the number of Leap Years between two dates in Excel
Keeping data on leap years in a list is important for businesses, government offices, and event organizers. If you need to check the number of leap years between two dates in Excel, please read this article.
A leap year has one day more than a non-leap year, but it affects many calculations. The payroll of many companies is different for leap years, profit calculations for companies are impacted by leap years.
Calculate the number of leap years between two dates in Excel
The syntax for counting the number of leap years between two dates in Excel is as follows:
=DATE(YEAR(| ),1,1)-DATE(YEAR( | ),1,1)-((YEAR( | )-YEAR( | ))*365)+AND(MONTH(DATE(YEAR( | ),2,29))=2,MONTH(DATE(YEAR( | ),2,29))=2)*1 |
Or,
The formula must be entered in the cell where you need the result (the number of leap years between two dates in Excel).
For example, if we need to calculate the number of leap years between March 23, 1964 and January 12, 2018, we first write the dates in MM / DD / YYYY format as follows:
- Start date: 23/03/1964
- End date: 01/12/2018
Suppose that the start date is in cell B4, the end date is in cell C4 and that the number of leap years between these two dates is necessary in cell E4, the formula would become:
=DATE(YEAR(C4),1,1)-DATE(YEAR(B4),1,1)-((YEAR(C4)-YEAR(B4))*365)+AND(MONTH(DATE(YEAR(B4),2,29))=2,MONTH(DATE(YEAR(C4),2,29))=2)*1
Enter this formula in cell E4 and click anywhere outside of this cell.
You will get the number of leap years between these two dates.
Count the number of leap years between a list of two dates in Excel
If you have a list of start and end dates in an Excel sheet, you can get the number of leap years between the list of two dates in a column using the fill option.
For example. If the list of start dates is in column B, the list of end dates is in column C and you need to find the list of leap year counts in column E for the corresponding start and end dates in the same line, use the same formula explained above, then use the fill option to draw the results on column E.
I hope it helps!