Adjusting Threat Level Formula
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?
Best Answer
-
=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.
Answers
-
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"))
-
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
-
=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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!