RYG "Yellow" Error for Health Metrics in Summary Row
Hi Community,
I am trying to create an overall health metric for a project based on the information in the summary row of the schedule. The criteria is a mix of fields:
- If the Status is on Hold = Blue
- If the Status is Complete = Blank
- If the Finish date has passed = Red
- If Today is within 7 days of the Finish Date and % Complete is less than 0.75 = Yellow
These are my columns:
And I'm using this formula.
This ALMOST does what I want it to, except the "Yellow" classification is off. I'm not sure how to make it measure between two dates. It is showing that my Health is yellow because the Finish Date is greater than 7 days and less 75% but I want it to only show yellow if its within those 7 days. Help!
This might be a stretch but is there a way to make that window of time proportionate to the duration of the project? For example, if it is within 75% of the total duration and less than 75% complete, then yellow?
Thank you in advance!
Best Answer
-
Hi Tamara,
There are two different conditions you have for the same colour. If your objective for yellow is if the finish date is within 7 days with less than 75% completion, you can write something like this "=IF(AND(Finish@row < TODAY(7), [% Complete]@row < 0.75), "Yellow") and you can remove the condition on start date for yellow as well.
Hope this helps
Thanks,
Aravind
Associate Director
Copernicus Consulting Pte. Ltd.
P: +65 9230 5657 | E: aravind@copernicusworld.com
Feel free to reach out for licenses, services, and training on Smartsheet
Answers
-
Hi Tamara,
There are two different conditions you have for the same colour. If your objective for yellow is if the finish date is within 7 days with less than 75% completion, you can write something like this "=IF(AND(Finish@row < TODAY(7), [% Complete]@row < 0.75), "Yellow") and you can remove the condition on start date for yellow as well.
Hope this helps
Thanks,
Aravind
Associate Director
Copernicus Consulting Pte. Ltd.
P: +65 9230 5657 | E: aravind@copernicusworld.com
Feel free to reach out for licenses, services, and training on Smartsheet
-
This worked beautifully Aravind. Thank you so much!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 377 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!