CODEWITHSUNDEEP
if-statementgoogle-sheetsarray-formulas
Neo and One
Neo and One

Reputation: 17

Google Sheet ARRAYFORMULA(IF(ISBLANK returning error

I am updating my array formula but the updated formula is erring out.

Original formula: =ARRAYFORMULA(IF(TODAY()>O2:O,"Past due",IF(TODAY()<O2:O-7,"Upcoming due date","Due this week")))

Updated formula: =ARRAYFORMULA(IF(ISBLANK(O2:O),"Not Started",IF(I2:I="Delivered","Delivered",IF(((TODAY()>O2:O)*(I2:I<>Delivered,"Past due",IF(TODAY()<O2:O-7,"Upcoming due date","Due this week")))))))

What I want this formula to do is:

  1. If Column O is blank, display text "Not Started"
  2. If Column O is not blank and today's date is greater than Column O AND Column I doesn't equal "Delivered", display text "Past Due"
  3. If Column I is "Delivered", display text "Delivered"
  4. IF(TODAY()<O2:O-7,"Upcoming due date","Due this week"

I am sharing my workbook, URL hyperlink available. The formula will live in Column AE "Due Week". Assistance needed and appreciated.

Google sheet

Upvotes: 0

Views: 1748

Answers (1)

Jeremy Kahan
Jeremy Kahan

Reputation: 3826

=ARRAYFORMULA(IF(ISBLANK(O2:O),"Not Started",IF(I2:I = "Delivered","Delivered",IF(TODAY()>O2:O,"Past due",IF(TODAY()<O2:O-7,"Upcoming due date","Due this week")))))

You had the right idea. Once you get that deep in, you know I2:I is not delivered, so that triple parentheses and multiplication with today should not be needed.Also, I think you needed quotes around "Delivered" where you said <>"Delivered". The * should work, but simplifying it with an AND helped me fix your formula to

    =ARRAYFORMULA(IF(ISBLANK(O2:O),"Not Started",IF(I2:I="Delivered","Delivered",IF(AND(TODAY()>O2:O,I2:I<>"Delivered"),"Past
 due",IF(TODAY()<O2:O-7,"Upcoming due date","Due this week")))))

Still no need, because we know it's not "Delivered" when we are in that deep.

Upvotes: 1

Related Questions