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
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:
A2-A1+1
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 2193A1
which is the start date with this range and subtracting with 1
to show the starting from 1/1/2024
till 1/1/2030
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
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")))