Counting days between two dates, but ignore leap year

[tikitour]

I have a spreadsheet that has a start and end date. It's easy enough to work out the difference "in days" between these two dates, however when the dates fall within a leap year it adds the extra day.  I do NOT want the formular to add this extra day, and I can't simple minus a day, because not all dates fall within the leap year.

So how do I tell excel to ignore the leap year (and NOT add the extra day)? 

Examples

a) 19/03/23 to 19/03/24 = 366 days, & ignore the leap year extra day =365 days

b) 21/10/23 to 20/04/24 = 182 days, & ignore the leap year extra day =181 days

C) 19/12/21 to 19/12/22 = 365 days (no leap year, so no problem)

[#2]

@tikitour 

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! 

[#3]

Thanks Niko. tried this, and it didn't subtract the extra day.

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

[#4]

@tikitour 

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:

1. a) Start Date: 19/03/2023 End Date: 19/03/2024 Result: 365 days
2. b) Start Date: 21/10/2023 End Date: 20/04/2024 Result: 181 days
3. 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 🙂

[#5]

@NikolinoDE 

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.