Nested IFs with multiple conditions

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.

Answers

  • Jeff Reisman
    Jeff Reisman Community Champion
    edited 02/10/22

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

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Thanks, I ended up using the following formula to get it to work.

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!