Adjusting Threat Level Formula

MD_Accel 23

I have a formula to set the threat level based on my status and milestone date.

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

I want my status to be green when the course status percentage is met and if todays date is further out than 30 days of the milestone date.

Is it possible to adjust the formula to change the status?

MD_Accel 23

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

It looks like this worked ….but upon updating the milestone date for the next phase, the status stayed yellow versus changing to green like I expected.

TrevRCincySheetz

Try this

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

MD_Accel 23

Hi,

@TrevRCincySheetz thank you! After reviewing with my team, they asked for some slight tweaking to the timing that would trigger the threat level.

Desired output: Green status - if 10% is reached on or before the milestone date, Amber status - if 10% is not reached within 2 weeks of target date, Red status - if 10% is not after 2 weeks of target date.

Following the guidance of your suggestion, I put this together but my threat level shouldn't be green, it should be yellow. Could you point out what I can update to my formula? I considered using IFS versus IF but I wasn't successful.

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

Kind regards

MD_Accel 23

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

It looks like this worked ….but upon updating the milestone date for the next phase, the status stayed yellow versus changing to green like I expected.