Excel formula shows up as text or problem with formula popup

I am using the following excel 2019 formula in cell b2:

=IF(AND(A1<>"", A2<>""), COUNTIFS(DATE(YEAR(A1), 2, 29), "<=" & A2, DATE(YEAR(A1), 2, 29), ">=" & A1), 0)

in cells a1 and a2 I have 2 dates 1/12024 and 1/1/2030. I’m expecting to get back 2 for the number of leap days found between the two dates.

I tried an online excel formula checker and got back:

= IF(
     AND(
         A1 <> "",
         A2 <> ""
     ),
     COUNTIFS(
              DATE(
                   YEAR(A1),
                   2,
                   29
              ),
              "<=" & A2,
              DATE(
                   YEAR(A1),
                   2,
                   29
              ),
              ">=" & A1
     ),
     0
)

and it says it’s a valid formula.

I have a column of dates and I’m trying to get the number of leap days between the rolling dates

>Solution :

Your existing formula should return that pop-up, this is because the IF() logic doesn’t follows the generic syntax as described in Microsoft Documentations. However, you could try using the following formula which would certainly return the desired output as is expected:

• Formula used in cell B3

=SUMPRODUCT(N(TEXT(A1+ROW($ZZ$1:INDEX($Z:$Z,A2-A1+1))-1,"dd-mm")="29-02"))

To explain the logic in the above formula:

• Firstly, we are extracting the number of days between two dates i.e. A2-A1+1
• Secondly, using those numbers of days within the INDEX() function to create a range of rows wrapped within ROW() . This returns ROW($ZZ$1:INDEX($Z:$Z,A2-A1+1)) 1,2,3,till 2193
• Thirdly, adding the first cell A1 which is the start date with this range and subtracting with 1 to show the starting from 1/1/2024 till 1/1/2030
• Next, wrapping within TEXT() function to show the dates as dd-mm which is then verified whether it is equal to the 29th of Feb each year. If so then it returns TRUE else FALSE
• Lastly, wrap within SUM() or SUMPRODUCT() to get the binary counts of TRUE

Also, if anyone of the cells are empty then can use the following as well:

=IF(OR(A1="",A2=""),"",SUMPRODUCT(N(TEXT(A1+ROW($ZZ$1:INDEX($Z:$Z,A2-A1+1))-1,"dd-mm")="29-02")))