Nested IFs with multiple conditions

TraciL

I am trying to do different comparisons based on different conditions. Below is the basic logic.

IF SO Status = Open 

Compare Due Date to TODAY's Date

If Due Date > TODAY's Date, Delivery Status = Past Due Else

 Delivery Status = On-Time

IF SO Status = Closed

Compare Ship Date to Due Date

If Ship Date > Due Date, Delivery Status = Past Due Else

 Delivery Status = On-Time

Below is the formula that is coming back with an Unparseable error:

=IF(AND([SO Status]@row = "Open", [Due Date]@row > TODAY()), "Past Due", "On-Time"), IF(AND([SO Status]@row = "Closed", [Ship Date] > [Due Date]@row, "Past Due", "On Time))

Any help is much appreciated! Thanks.

Jeff Reisman

The way IF statements work is of course "IF <criteria> is true, positive condition, otherwise, negative condition."

The way nested IFs work best is the the first nested IF represents the negative condition of the first IF, and so on.

What's happening in your formula is that you are closing out the first IF completely with positive and negative conditions, and then starting another completely separate IF. That's not going to work. You're also missing some things, like the @row after [Ship Date], the closing parentheses on the second AND statement, and the closing quotes on the last "On Time".

With your logic, let's map out the IF and the nested IFs:

Criteria 1: IF SO Status = Open

Positive Condition, because SO Status = OPEN, is a nested IF: IF Due Date is greater than TODAY, set the Delivery Status to Past Due, otherwise set it to On Time.

Negative condition, because SO Status does not equal OPEN is another nested IF: IF SO Status = CLOSED and Ship Date is greater than Due Date, set the Delivery status to Past Due, otherwise set it to On Time

Now we write it out:

=IF([SO Status]@row = "Open", IF([Due Date]@row > TODAY()), "Past Due", "On-Time"), IF(AND([SO Status]@row = "Closed", [Ship Date]@row > [Due Date]@row), "Past Due", "On Time"))

Ideally, we should also wrap this whole thing in an IFERROR; in case there's no SO Status value or a value other than Open or Closed, the cell will list "No SO Status or Invalid Status." (you can set this to be whatever you want it to show in case of an error.)

=IFERROR(IF([SO Status]@row = "Open", IF([Due Date]@row > TODAY()), "Past Due", "On-Time"), IF(AND([SO Status]@row = "Closed", [Ship Date]@row > [Due Date]@row), "Past Due", "On Time")), "No SO Status or Invalid Status")