Updating threat level based on percent complete
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
Best Answer
-
=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.
...
Answers
-
=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.
...
-
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.
-
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", ""))
...
-
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.
-
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", ""))
-
=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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!