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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.2K Get Help
- 430 Global Discussions
- 150 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 500 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!