google-sheets

Conditional formatting if cell past date and another cell empty with arrayformula


I'm using a formula to add TRUE/FALSE when the statement is true.

enter image description here

Rather than add this formula to every row (I have over 4000), I want to add it to the 2nd row as an arrayformula. But I write the formula like this:

=ARRAYFORMULA(AND(F2:F<=TODAY()-21,F2:F<>"",H2:H = "")) the formula doens't apply to the entire column.

Expecting arrayformula to apply formula across the entire column.


Solution

  • Using (*) instead of AND Operator

    Instead of using the AND operator in your formula, use an asterisk instead. The AND operator does not work with ARRAYFORMULA and the asterisk (*) serves as an equivalent to AND. In this context, it allows you to perform logical AND operations within an ARRAYFORMULA.

    You may try this formula:

    =ARRAYFORMULA(IF(F2:F <> "", IF(H2:H = "", TODAY() - F2:F >= 21, H2:H - F2:F > 21), FALSE))
    

    Sample Output:

    output

    Reference:

    Google Sheets function list