Keeping data on the number of leap years in a list is important for companies, government offices, and event organizers. If you need to check for the number of leap years between two dates in Excel, please read through this article.
A leap year has one day more than a non-leap year, but it impacts a lot of calculations. The payroll for many companies is different for leap years, and the profit calculations for businesses are impacted by leap years.
Calculate the number of leap years between two dates in Excel
The syntax to count the number of leap years between two dates in Excel is as follows:
=DATE(YEAR(<cell with end date>),1,1)-DATE(YEAR(<cell with start date>),1,1)-((YEAR(<cell with end date>)-YEAR(<cell with start date>))*365)+AND(MONTH(DATE(YEAR(<cell with start date>),2,29))=2,MONTH(DATE(YEAR(<cell with end date>),2,29))=2)*1
Where <cell with start date> and <cell with end date> specify the first and last date of the period between which you need the count of leap years. The dates are to be in MM/DD/YYYY format.
The formula will 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 23rd March 1964 and 12th January 2018, we would first write the dates in MM/DD/YYYY format as follows:
- Start date: 03/23/1964
- End date: 01/12/2018
Let us assume the start date is in cell B4, the end date is in cell C4, and the count of the number of leap years between these two dates is needed 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 that cell.
You will get the count of the number of leap years between those two dates.
Count number of leap years between a list two dates in Excel
In case you have a list of start and end dates in an Excel sheet, you can get the count of the number of leap years between the list of two dates in a column using the fill option.
Eg. If the list of start dates are in column B, the list of end dates is in column C, and you need to find the list of counts of leap years in column E for the corresponding start and end dates in the same row, use the same formula explained above and then use the fill option to pull the results across column E.
I hope it helps!
Leave a Reply