Updating threat level based on percent complete

MD_Accel 23

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

heyjay

=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.

heyjay

=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.

MD_Accel 23

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.

heyjay

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

MD_Accel 23

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.

MD_Accel 23

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

heyjay

=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.