Red / Yellow / Amber formula

sahilhq

Hi,

Can anone please suggest for my sheet that is formatted as shown in the attached image and for the following conditions

• if risk/issues cell is not blank and due date is in the future then yellow
• if past due date then red
• if past due date and with risk/issue cell not blank still red
• if not past due date and no risk/issues text = green
• if a project is status complete it should turn green (regardless of other things if possible)

formula.PNG

Mark Cronk

Hi @sahilhq ,

You need to apply conditional formatting. You can search for the smartsheet tutorial. It's very simple to do. You just create a rule for each. When the rule is met it triggers the format response. You can format specific cells or the row.

Good luck.

Mark

Genevieve P.

Hi @sahilhq

Conditional formatting as Mark suggested would work well if you're looking to colour the background of the cells... however it sounds like you may be wanting to automate the status ball in your "Project Health" column, is that correct?

If so, you can build a Nested IF statement to achieve this! I'll go through each of your logic points below to show when it will turn each colour, then we'll put it all together in one final formula at the end.

Keep in mind that Logic Formulas start by looking through each statement left-to-right and stop as soon as the criteria is met. Because of this, we can arrange the order of your instructions to first check if the status is complete, and only change to other colours if it's not complete.

Note: this formula presumes your Risk/Issues column is titled "Risk/Issues" (it's not in the screen capture to verify this).

Green

• if a project is status complete it should turn green (regardless of other things if possible)

=IF(Status@row = "Complete", "Green",

Red

• if past due date then red
• if past due date and with risk/issue cell not blank still red

IF([Due Date]@row < TODAY(), "Red",

Yellow

• if risk/issues cell is not blank and due date is in the future then yellow

Since you've already specified that any due dates in the PAST will be red, you don't need to put this in your yellow statement. Instead, you can just look for the "Risk/Issues", and it will automatically also look to see if the due date is Today or in the Future.

IF([Risk/Issues]@row <> "", "Yellow",

Green again

• if not past due date and no risk/issues text = green

The rules above cover what happens if the date is in the past or if the Risk/Issues column has content, so all we need to do is to say that the default colour is green:

"Green"

FULL FORMULA

=IF(Status@row = "Complete", "Green", IF([Due Date]@row < TODAY(), "Red", IF([Risk/Issues]@row <> "", "Yellow", "Green")))

Let me know if this works for you!

Cheers,

Genevieve

Mark Cronk

Great answer @Genevieve P . Mark