How To Have Risk Column Automatically Update Based On Certain Conditions

David Bramer

Hello,

Can anyone help me write a formula based on the following conditions.

Risk Status = "Green" if the following conditions are met:

[Delivery Confirmed] is checked

Risk Status = "Gray" if the following conditions are met:

[Release Date] is Blank

Risk Status = "Red" if any of the following conditions are met below:

[Expected Delivery Date] is greater than [Required Delivery Date]
[Quantity Missing] does NOT equal 0 AND IF [Required Delivery Date] is less than Today's Date

Risk Status = "Yellow" if any of the following conditions are met below:

[Expected Delivery Date] is 15 or more days LESS THAN [Required Delivery Date]

Smartsheet Screenshot.pdf

Find more posts tagged with

Construction

Project Management

Scheduling

Accepted answers

David Bramer

Thank you to everyone who helped the formula below is what worked for my application:

=IF(OR([Delivery Confirmed]@row = 1, [Quantity Missing]@row = 0), "Green", IF([Vendor: Expected Delivery Date]@row < [HC: Required Delivery Date]@row - 15, "Yellow", IF(AND([Vendor: Expected Delivery Date]@row > [HC: Required Delivery Date]@row, [Quantity Missing]@row <> 0), "Red", IF(AND([HC: Required Delivery Date]@row < TODAY(), [Quantity Missing]@row <> 0), "Red", "Gray"))))

All comments

sfcrews

You will need imbedded IF statements- something like below- maybe this will be close enough for AI to fix…

=If([Delivery Confirmed]="Yes","Green",IF(and([Expected Delivery Date]>[Required Delivery Date],[Quantity Missing]<>0,[Required Delivery Date]<today()),"Red",IF([Expected Delivery Date]>[Required Delivery Date]-15),"Yellow","")

Adam Murphy

Try this, should be a good start:

=IF([Delivery Confirmed]@row = 1, "Green", IF(ISBLANK([Release Date]@row), "Gray", IF([Required Delivery Date]@row - [Expected Delivery Date]@row > 15, "Yellow", IF([Expected Delivery Date]@row > [Required Delivery Date]@row, "Red", IF(AND([Quantity Missing]@row <> 0, [Required Delivery Date]@row < TODAY()), "Red", "")))))

David Bramer

Thank you to everyone who helped the formula below is what worked for my application:

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