Using decision symbols: Yes, No & Hold

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 😊

Find more posts tagged with

Sheets

Solution Center

Accepted answers

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.

All comments

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

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. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.