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

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

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:

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