DATEDIF FUNCTION not functioning as expected-CodePudding

What's the reason this array formula (in cell H1) is not producing the indented result of showing the age range of each person at the time of "First Call"?

={"Array";arrayformula(IF(DATEDIF(F2:F, C2:C, "Y") < 25, "18-24",
IF(AND((DATEDIF(F2:F, C2:C, "Y") >= 25), (DATEDIF(F2:F, C2:C, "Y") < 35)), "25-34",
IF(AND(DATEDIF(F2:F, C2:C, "Y") >= 35, DATEDIF(F2:F, C2:C, "Y") < 45), "35-44",
IF(AND(DATEDIF(F2:F, C2:C, "Y") >= 45, DATEDIF(F2:F, C2:C, "Y") < 55), "45-54", "55 ")))))}

I tested the formula outside of an array (in column G) to check for errors and it worked as expected

=IF(DATEDIF(F2, C2, "Y") < 25, "18-24",
IF(AND((DATEDIF(F2, C2, "Y") >= 25), (DATEDIF(F2, C2, "Y") < 35)), "25-34",
IF(AND(DATEDIF(F2, C2, "Y") >= 35, DATEDIF(F2, C2, "Y") < 45), "35-44",
IF(AND(DATEDIF(F2, C2, "Y") >= 45, DATEDIF(F2, C2, "Y") < 55), "45-54", "55 "))))

CodePudding user response：

Within Arrayformula in sheets use * instead of AND

={"Array";arrayformula(IF(DATEDIF(F2:F, C2:C, "Y") < 25, "18-24",
IF((DATEDIF(F2:F, C2:C, "Y") >= 25) * (DATEDIF(F2:F, C2:C, "Y") < 35), "25-34",
IF((DATEDIF(F2:F, C2:C, "Y") >= 35) * (DATEDIF(F2:F, C2:C, "Y") < 45),  "35-44",
IF((DATEDIF(F2:F, C2:C, "Y") >= 45) * (DATEDIF(F2:F, C2:C, "Y") < 55),  "45-54", "55 ")))))}