Set RAG based on % Complete & Deadline Date Proximity
Hi
I am trying to get my formula to set a RAG status based on the % complete and deadline date proximity. My formula is:
However, the last entry with a deadline date of 12/11 show be showing a "Yellow" RAG but, it still shows "Red". What have I missed?
TIA
Cheryl
Best Answer
-
Hey Cheryl,
You can simplify this a bit:
=IF([Deadline Date]@row > (TODAY() + 21), "Green", IF(AND([% Complete]@row <>"Full", [Deadline Date]@row > (TODAY() + 10)), "Yellow", "Red"))
(next time, please copy/paste your formula 😊)
You can utilize "<>" which means "is not equal to", so for this formula, you can say "The percent complete column is not equal to 'Full'".
---
However, your 12/11/21 date is 43 days from today (10/29/21), so it technically should be 🟢Green, not 🟡Yellow - right? Since your initial IF statement says that cells with a value higher than Today's date + 21 days should be Green, that one should be 🟢Green, right?
A date like 11/11/21 would be 🟡Yellow since it'd be within the 21 days (13 days from today's date).
---
Let me know if this works for you!
If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.
Love,
Brett Wyrick | Connect with me on LinkedIn.
------------------------------------------------------------------------------
2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!
Answers
-
Hey Cheryl,
You can simplify this a bit:
=IF([Deadline Date]@row > (TODAY() + 21), "Green", IF(AND([% Complete]@row <>"Full", [Deadline Date]@row > (TODAY() + 10)), "Yellow", "Red"))
(next time, please copy/paste your formula 😊)
You can utilize "<>" which means "is not equal to", so for this formula, you can say "The percent complete column is not equal to 'Full'".
---
However, your 12/11/21 date is 43 days from today (10/29/21), so it technically should be 🟢Green, not 🟡Yellow - right? Since your initial IF statement says that cells with a value higher than Today's date + 21 days should be Green, that one should be 🟢Green, right?
A date like 11/11/21 would be 🟡Yellow since it'd be within the 21 days (13 days from today's date).
---
Let me know if this works for you!
If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.
Love,
Brett Wyrick | Connect with me on LinkedIn.
------------------------------------------------------------------------------
2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!