Forum Discussion
Counting days between two dates, but ignore leap year
You can use a formula in Excel to calculate the number of days between two dates while ignoring leap years.
Here’s an example of how you could do this:
=DATEDIF(A2,B2,"d")-IF(AND(MONTH(A2)<=2,MONTH(B2)>=3,YEAR(A2)=YEAR(B2)),1,0)
In this example, A2 and B2 are the cells that contain the start and end dates, respectively. The DATEDIF function calculates the number of days between the two dates. The IF function checks if the start date is before or on February and the end date is on or after March of the same year (i.e., the date range includes February 29th of a leap year). If this condition is true, the formula subtracts 1 from the result of the DATEDIF function to ignore the extra day added by the leap year.
You can adjust this formula to fit your specific needs by changing the cell references and adding additional conditions as needed.
I hope this helps!
=DATEDIF(P41,Q41,"d")-IF(AND(MONTH(P41)<=2,MONTH(Q41)>=3,YEAR(P41)=YEAR(Q41)),1,0)
(cell p41) Start Date 21/10/2023 (cell Q41) End Date 21/10/2024
But still calculating 366 days, and including the leap year.
You can modify the formula to check if the date range includes February 29th of a leap year and subtract 1 from the total number of days calculated by the DATEDIF function if it does.
Here's an example formula that should work for you:
=DATEDIF(P41,Q41,"d")-IF(AND(MONTH(P41)<=2,MONTH(Q41)>=3,YEAR(P41)=YEAR(Q41),OR(MOD(YEAR(P41),400)=0,AND(MOD(YEAR(P41),4)=0,MOD(YEAR(P41),100)<>0))),1,0)
This formula checks if the start date is before or on February and the end date is on or after March of the same year.
If this condition is true, it then checks if the year is a leap year by using the OR and AND functions along with the MOD function to check if the year is divisible by 400 or divisible by 4 but not divisible by 100.
If this condition is also true, it subtracts 1 from the total number of days calculated by the DATEDIF function.
Using this formula with your examples:
- a) Start Date: 19/03/2023 End Date: 19/03/2024 Result: 365 days
- b) Start Date: 21/10/2023 End Date: 20/04/2024 Result: 181 days
- c) Start Date: 19/12/2021 End Date: 19/12/2022 Result: 365 days
I hope this helps! … and I got it right, this time 🙂
Thanks, you clearly know your stuff, and sadly I do not. Sad thing for me is it still does not want to minus that 1 day.
