Leap Year Issue

[ankit4mi]

I am trying to put a formula where i can find out the count of leap years between 2 given dates. for example
A2 - 09/10/2014
C23 - 09/10/2020
Formula which I am using is - DATE(YEAR(C23),1,1)-DATE(YEAR(A2),1,1)-((YEAR(C23)-YEAR(A2))*365)+AND(MONTH(DATE(YEAR(A2),2,29))=2,MONTH(DATE(YEAR(C23),2,29))=2)*1
As of now the result is coming is 1 but there are two leap years between these 2 dates i.e. 2016 & 2020. I don't understood where is the formula going wrong.

 

[Habtest]

The first part is ignoring the potential leap day in the more recent year (in this case 2020-2-29),

DATE(YEAR(C23),1,1)-DATE(YEAR(A2),1,1)-((YEAR(C23)-YEAR(A2))*365)

while the second part is not adding that back (2014 is not a leap year, so result of AND() is 0)

AND(MONTH(DATE(YEAR(A2),2,29))=2,MONTH(DATE(YEAR(C23),2,29))=2)*1

Maybe try this

=SUM(--(MONTH(DATE(ROW(INDIRECT((YEAR(A2)+--(MONTH(A2)>2)&":"&YEAR(C23)---(MONTH(C23)<3)))),2,29))=2))

 

[Fluff]

Another option

=SUM(--(MONTH(DATE(SEQUENCE(YEAR(C23)-YEAR(A2)+1,,YEAR(A2)),2,29))=2))

 

[Dave Patton]

another option
= SUMPRODUCT(--(TEXT(ROW(INDIRECT(A2&":"&C23)),"ddmmm")="29Feb"))

 

[Alex Blakenburg]

Another option

=SUM(--(MONTH(DATE(SEQUENCE(YEAR(C23)-YEAR(A2)+1,,YEAR(A2)),2,29))=2))

This was a great exercise for me in learning more about sequence but this sequence formula gives a different result to the others.
It includes the whole year regardless of whether the "starting date was after 29/02" or "the ending date was before 29/02".

So for:
10 Mar 2000 to 09 Oct 2020 the other formulas give 5 but this gives 6
01 Feb 2000 to 10 Feb 2020 the other formulas give 5 but this gives 6

 

[Fluff]

It includes the whole year regardless of whether the "starting date was after 29/02" or "the ending date was before 29/02".

Yes, but that's because a leap year is an entire year therefore start & end dates are irrelevant other than the year.

 

[Alex Blakenburg]

Yes, but that's because a leap year is an entire year therefore start & end dates are irrelevant other than the year.

Thanks for clarifying.