Counting the number of leap years between 2 dates

[polarisking]

I consider myself a fairly advance user, but I need help understanding what this formula is doing

=SUMPRODUCT(--ISNUMBER(--(ROW(INDIRECT(YEAR(StartDate)&":"&YEAR(EndDate)))&"-02-29")))

For StartDate, use 10/1/2001.
For EndDate, use 2/1/2014.

The correct answer is 3 (2004, 2008, and 2012) and that's exactly what this formula returns.

It must be counting the number of TRUE occurrences in an array of dates where 02-29-YYYY is valid, but I can't fully understand what's happening "under the covers." Any assistance would be much appreciated.

 

[GraH - Guido]

The ":" is a from-to operator (like in a range).
Year(Start) gives you 2001 and year(End) 2014.
The range 2001:2014 is created, but in text format.
Indirect converts this to a real range.
Row turns this into an array of numbers.
Adding "-02-29" forces this to be dates. But again as text.
The second "--" converts this to numbers, except where the dates are impossible, this throws an error.
IsNumber will test of these strings are numbers (real dates).
The first "--" turns trues and falses to 1 and 0.
Sumproduct can do the sum (as there is no product part).

In modern excel, this is possible.
=SUM((TEXT(SEQUENCE(EndDate-StartDate+1;;F3;1);"dd/mm")="29/02")*1)

 

[polarisking]

The ":" is a from-to operator (like in a range).
Year(Start) gives you 2001 and year(End) 2014.
The range 2001:2014 is created, but in text format.
Indirect converts this to a real range.
Row turns this into an array of numbers.
Adding "-02-29" forces this to be dates. But again as text.
The second "--" converts this to numbers, except where the dates are impossible, this throws an error.
IsNumber will test of these strings are numbers (real dates).
The first "--" turns trues and falses to 1 and 0.
Sumproduct can do the sum (as there is no product part).

In modern excel, this is possible.
=SUM((TEXT(SEQUENCE(EndDate-StartDate+1;;F3;1);"dd/mm")="29/02")*1)

Exactly what I needed. Thank you.

 

[Peter Bartholomew]

@GraH - Guido Good description!
Staying totally in the realm of numbers with 365

= LET(
    startYear,  YEAR(startDate),
    endYear,    YEAR(endDate),
    years,      SEQUENCE(1 + endYear - startYear,,startYear),
    lastDayFeb, DATE(years, 3, 0),
    leapYears,  FILTER(years, DAY(lastDayFeb) = 29),
    COUNT(leapYears)
  )

The trick there is to use the zeroth day of March to return the final day of February.
With sufficient effort, I can always make a formula longer!

 

[GraH - Guido]

Hi @Peter Bartholomew , nice one. I figured out I could have use the year range approach after posting my first attempt at it. Then forgot about it rather quickly.