Change risk color per multiple variables

Indiemusicboy

Hello. Need some help with a multi variable formula.

• I have a RISK column. The column type is Symbols (Red, Yellow, Green).
• I have an END date column.
• I have a STATUS column (Not Started, In Progress, Complete)

I'd like create a formula that by default the RISK is Green (green circle). If it is 2 days past END date it goes Yellow (yellow triangle) and if it is 4 days past END date it goes Red (red octagon).

Here is the formula I started but it needs more variables.

=IF(AND(End@row >= TODAY(-4), Status@row = "In Progress"), "Green", "Red")

Essentially, I want it to say:

"If END DATE is 4 days past due, and the STATUS is "In Progress" or "Not Started, then change RISK to RED, otherwise it should be GREEN.

Paul Newcome

Laying it all out definitely helps now that I see what all of your criteria is. Give this a try:

=IF(OR(Status@row = "Complete", Status@row = "Canceled", AND(Status@row = "In Progress", End@row>= TODAY())), "Green", IF(OR(AND(Status@row = "In Progress", End@row<= TODAY(-2)), AND(Status@row = "Not Started", Start@row<= TODAY(-2))), "Red", "Yellow"))

Paul Newcome

Try this:

=IF(Status@row = "Complete", "Green", IF(End@row<= TODAY(-4), "Red", "Yellow"))

Indiemusicboy

https://community.smartsheet.com/discussion/comment/356998#Comment_356998

This ALMOST works. Thank you. The only issue I still have is if STATUS is "In Progress" and END date is greater than today, the RISK is yellow. I'd like it to be GREEN.

Indiemusicboy

https://community.smartsheet.com/discussion/comment/356998#Comment_356998

@Paul Newcome I've added a screen shot to help. I'm wondering if this is an IF/OR/AND situation? I was going to try to manipulate your solution to add more criteria but perhaps I should just lay it all out.

Green Criteria:

1. END date is before today's date and STATUS is "In Progress"
2. STATUS is "Complete"
3. STATUS is "Canceled"

Yellow Criteria:

1. END date is (1) day after today's date and STATUS is "In Progress"
2. START date is (1) day after date listed and STATUS is "Not Started"
3. STATUS is "On Hold"

Red Criteria:

1. END date is (2) days after today's date and STATUS is "In Progress"
2. START date is (2) days after date listed and STATUS is "Not Started"

Paul Newcome

Laying it all out definitely helps now that I see what all of your criteria is. Give this a try:

=IF(OR(Status@row = "Complete", Status@row = "Canceled", AND(Status@row = "In Progress", End@row>= TODAY())), "Green", IF(OR(AND(Status@row = "In Progress", End@row<= TODAY(-2)), AND(Status@row = "Not Started", Start@row<= TODAY(-2))), "Red", "Yellow"))

Indiemusicboy

@Paul Newcome you helped me with the below formula. Much appreciated. However I've run into a real world problem in that I also want the color to be RED if a START@row date is past today and STATUS@row is "NOT STARTED". I've tried to add additional code but it just gives me errors. Could you (or anyone for that matter) assist?

=IF(OR(Status@row = "Complete", Status@row = "Canceled", Status@row = "Not Started", AND(Status@row = "In Progress", End@row >= TODAY())), "Green", IF(OR(AND(Status@row = "In Progress", End@row <= TODAY(-2)), AND(Status@row = "Not Started", Start@row <= TODAY(-2))), "Red", "Yellow"))

Paul Newcome

Since we already have it set to turn read if it is Not Started and the start date is two or more days in the past, then we can just tweak that portion of the formula to read in the past instead of at least two days in the past.

=IF(OR(Status@row = "Complete", Status@row = "Canceled", AND(Status@row = "In Progress", End@row>= TODAY())), "Green", IF(OR(AND(Status@row = "In Progress", End@row<= TODAY(-2)), AND(Status@row = "Not Started", Start@row<= TODAY())), "Red", "Yellow"))