# Updating threat level based on percent complete

Options

Hi,

Could someone help guide me with what is wrong with my formula?

What I am looking for the formula to output:

If the Percent complete is less than 30% within 30 days of the Current milestone date, Threat level is a yellow symbol. If the Percent complete does not equal 30%, today is after Current milestone date, the Threat level is a red symbol.

Current attempt:

=IF(AND([Percent complete]@row < 30, TODAY() <= ([Current milestone date]@row + 30)),"Yellow", IF(AND([Percent complete]@row <> 30, TODAY() >[Current milestone date]@row), "Red", ""))

Kind regards

• ✭✭✭✭✭
Options

```=IF(AND([Percent complete]@row <> 0.3, TODAY() > [Current milestone date]@row), "Red",
IF(AND([Percent complete]@row < 0.3, TODAY() <= ([Current milestone date]@row + 30)), "Yellow",
"Green"))
```

Same formula as the previous but making use of the "'otherwise' make it green" at the end.

...

• ✭✭✭✭✭
edited 07/25/24
Options
```=IF(AND([Percent complete]@row <> 0.3, TODAY() > [Current milestone date]@row), "Red",
IF(AND([Percent complete]@row < 0.3, TODAY() <= ([Current milestone date]@row + 30)), "Yellow", ""))

```

Please use decimal number in your formula when comparing to percentages in cells. Swap the RED and YELLOW formula.

...

• Options

Thank you so much! It worked, kindly appreciate your help @heyjay !

Is there a way to set the threat level to be a green status? I tried to set this up with an automation but symbols aren't an option for a cell value change in automation.

• ✭✭✭✭✭
Options

Using a formula? Yes, you just need to add another criteria for green, replace the items in bold fontface.

```=IF(AND([Percent complete]@row <> 0.3, TODAY() > [Current milestone date]@row), "Red",
IF(AND([Percent complete]@row < 0.3, TODAY() <= ([Current milestone date]@row + 30)), "Yellow",
IF(AND([Percent complete]@row < 0.3, TODAY() <= ([Current milestone date]@row + 30)), "Green",
""))
```

...

• Options

My apologies, if using a formula I would look to have it be at green status if it is not at a yellow or red status.

• Options

I tried something like so…

=IF([Threat Level]@row <>"Red","Yellow",TODAY()<=([Currentmilestone date]@row +30)), "Green", IF(AND([Percentcomplete]@row <>0.3, TODAY() > [Current milestone date]@row), "Red", IF(AND([Percentcomplete]@row <0.3, TODAY() <= ([Currentmilestone date]@row +30)), "Yellow", ""))

• ✭✭✭✭✭
Options

```=IF(AND([Percent complete]@row <> 0.3, TODAY() > [Current milestone date]@row), "Red",
IF(AND([Percent complete]@row < 0.3, TODAY() <= ([Current milestone date]@row + 30)), "Yellow",
"Green"))
```

Same formula as the previous but making use of the "'otherwise' make it green" at the end.

...

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!