Using decision symbols: Yes, No & Hold

Jessica Defilippis

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

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

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

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

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

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

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

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

Always happy to help. 👍️