Looking for formula

need to populate a date with these parameters.

Defect type ="CRITICAL" - populates "clean up date" with date 5 days after "audit received date"

but if Defect type = "major" or "minor" - populates "clean up date" with date 30 days after "audit received date"

right now i have simple formula to populate Clean up date as 30 days after audit date. But the defect type changes that date parameter to 5 days out instead of 30 days out. Is there formula to do that, maybe IF?

Find more posts tagged with

Accepted answers

Paul Newcome

=IF(CONTAINS("CRITICAL", [DEFECT TYPE]@row), [Audit received]@row + 5, IF(OR(CONTAINS("MAJOR", [DEFECT TYPE]@row), CONTAINS("MINOR", [DEFECT TYPE]@row)), [Audit received]@row + 30))

Give that a try.

All comments

BRYAN BRAY

i have this formula currently but get error

smart sheet audit .jpg

=IF([DEFECT TYPE]@row = "CRITICAL"; [Audit received]@row + 5; IF([DEFECT TYPE]@row = "MAJOR"; [Audit received]@row + 30; IF([DEFECT TYPE]@row = "MINOR"; [Audit received]@row + 30))

BRYAN BRAY

fixed it, had semi colon instead of comma

=IF([DEFECT TYPE]@row = "CRITICAL", [Audit received]@row + 5, IF([DEFECT TYPE]@row =

"MAJOR", [Audit received]@row + 30, IF([DEFECT TYPE]@row =

"MINOR", [Audit received]@row + 30))

but now i cant figure out how to change formula for a multi select drop down box

If DEFECT TYPE contains CRITICAL with any other combo of MAJOR OR MINOR. date is +5 days

If not CRITICAL - But any combo of MAJOR or MiNOR , date is +30

i only have it working on single selection drop down.

Paul Newcome

=IF(CONTAINS("CRITICAL", [DEFECT TYPE]@row), [Audit received]@row + 5, IF(OR(CONTAINS("MAJOR", [DEFECT TYPE]@row), CONTAINS("MINOR", [DEFECT TYPE]@row)), [Audit received]@row + 30))

Give that a try.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.