Perform calculation only if both cells are not blank with arrayformula?-CodePudding

i am having difficult understanding what went wrong with my logic in google sheet.

Imagine if i have two columns with dates (column A and G). And i have an empty column to perform the calculation of date difference (A and G), and includes the start/end date using array formula.

I written the following:

=ARRAYFORMULA(IF(AND(NOT(ISBLANK(A2:A)), NOT(ISBLANK(G2:G))),DAYS(A2:A,G2:G) 1))

but it return FALSE to me on the first row even both cell A and G has a date, and the array formula didn't get to fill out for the subsequent rows.

If I changed to the following using OR operator:

=ARRAYFORMULA(IF(OR(NOT(ISBLANK(A2:A)), NOT(ISBLANK(G2:G))),DAYS(A2:A,G2:G) 1))

It does work but not what i wish it to behave. If either column is filled, it produce -44468 or 44468; and if neither column is filled, it produce 1.

How can i fix my code above such that if both cell A and G is not blank, then only perform the calculation of the date difference, else return as NULL or blank?

And i wish to use arrayformula so that i don't have to copy the formula into every single rows in future.

CodePudding user response：

Do not use AND/OR with ARRAYFORMULA

=ARRAYFORMULA(IF(ISBLANK(A2:A)   ISBLANK(G2:G) > 0,,DAYS(A2:A,G2:G) 1))

CodePudding user response：

AND & OR operations are not supported under ARRAYFORMULA

instead of AND use *

=ARRAYFORMULA(IF((NOT(ISBLANK(A2:A))*(NOT(ISBLANK(G2:G)), DAYS(A2:A, G2:G) 1), )

instead of OR use

=ARRAYFORMULA(IF((NOT(ISBLANK(A2:A)) (NOT(ISBLANK(G2:G)), DAYS(A2:A, G2:G) 1), )