Using decision symbols: Yes, No & Hold

Jessica Defilippis · 06/17/21

Hi all,

Understand that this has probably been answered before however I can't quite find the solution I need and have spent a little too long trying to get this sorted.

I need to display Yes, No or Hold symbols with the following parameters:

If [Status@row] is equal to "Submitted" or "In-Progress" and [Created@row] (date) is less than [Expected completion date@row] then display Hold
If [Status@row] is equal to "Resolved" and [Created@row] (date) is less than [Expected completion date@row] then display Yes
If [Status@row] is equal to "Resolved" and [Created@row] (date) is more than [Expected completion date@row] then display No
If [Status@row] is equal to "Disputed" and [Created@row] (date) is less than [Expected completion date@row] then display Hold
If [Status@row] is equal to "Disputed" and [Created@row] (date) is more than [Expected completion date@row] then display Hold

Unsure how to make my formula work with so many parameters or if it is even possible.

Thank you in advance 😊

Paul Newcome · 06/17/21

Assuming that you only have 4 options for Status (Submitted, In-Progress, Resolved, and Disputed), we can actually simplify the formula a bit based on your exact criteria listed above.

=IF(Status@row = "Disputed", "Hold", IF(Created@row < [Expected Completion Date]@row, IF(Status@row = "Resolved", "Yes", "Hold"), IF(Status@row = "Resolved", "No")))

The only two things I see unaccounted for (maybe it isn't a possibility) would be where the Created date is greater than the Expected Completion Date and the Status is either "Submitted" or "In-Progress". The formulas above would output a blank for both of those scenarios.

Mark Cronk · 06/17/21

Try:

=IF(status@row="Disputed", "Hold", IF(AND(OR(Status@row= "Submitted", Status@row="In-Progress" ), Created@row< [Expected completion date]@row), "Hold", IF( AND(Status@row="Resolved", Created@row<[Expected completion date]@row), "Yes", IF(AND(Status@row= "Resolved", Created@row]> [Expected completion date]@row, "No", ""))))

Mark

Jessica Defilippis · 06/17/21

https://community.smartsheet.com/discussion/comment/295183#Comment_295183

Hi Mark,

Thanks however it appears to still not like the formula and returns a '#INCORRECT ARGUMENT SET' error.

Paul Newcome · 06/17/21

It looks like you may be missing some pieces from @Mark Cronk's formula and some of the parenthesis may be a little out of order. We need to make sure the OR and AND functions are being closed properly before moving on to the next portion of the IF statement.

Here is Marks formula with a couple of minor tweaks:

(Mark: You had an extra closing square bracket after the last "Created@row" and and a couple of extra spaces between functions)

=IF(Status@row = "Disputed", "Hold", IF(AND(OR(Status@row = "Submitted", Status@row = "In-Progress"), Created@row < [Expected completion date]@row), "Hold", IF(AND(Status@row = "Resolved", Created@row < [Expected completion date]@row), "Yes", IF(AND(Status@row = "Resolved", Created@row > [Expected completion date]@row, "No", ""))))

Paul Newcome · 06/17/21

Assuming that you only have 4 options for Status (Submitted, In-Progress, Resolved, and Disputed), we can actually simplify the formula a bit based on your exact criteria listed above.

=IF(Status@row = "Disputed", "Hold", IF(Created@row < [Expected Completion Date]@row, IF(Status@row = "Resolved", "Yes", "Hold"), IF(Status@row = "Resolved", "No")))

The only two things I see unaccounted for (maybe it isn't a possibility) would be where the Created date is greater than the Expected Completion Date and the Status is either "Submitted" or "In-Progress". The formulas above would output a blank for both of those scenarios.

Jessica Defilippis · 06/18/21

https://community.smartsheet.com/discussion/comment/295217#Comment_295217

Hi Paul,

Thank you for your help, it is missing a few scenarios however I can add them based on the formulas you have provided.

Much appreciated 😊

Paul Newcome · 06/18/21

Excellent. If you'd like help with the super consolidated version, feel free to let me know.

Always happy to help. 👍️

Using decision symbols: Yes, No & Hold

Best Answer

Answers

Help Article Resources

Categories