Search code examples
arraysgoogle-sheetsgoogle-sheets-formulaarray-formulas

DATEDIF FUNCTION not functioning as expected

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+")))))}

enter image description here



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+"))))

enter image description here

Solution

  • 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+")))))}