Schedule Health Based off Duration and % Complete
Hi - I'm trying to write a task health formula with the following criteria and am hoping someone can help. Here's what I'd like it to calculate:
- GREEN: If today is less than 50% of the task duration and % complete is more than 50%
- YELLOW: If today is more than 50% of the task duration and % complete is less than 50%
- RED: If today is more than 75% of the task duration and % complete is less than 75%
Thanks in advance!
Answers
-
Additional requirements:
-Green if start date is after todays date
-Red if end date is before todays date (if not 100% Complete)
-
Instead of worrying about set percentages (50% compared to 50% and 75% compared to 75%) it is actually easier to do a direct comparison of planned vs actual. So if it is supposed to be 27% based on duration, we change the color based on actual % Complete.
The only other thing to think about with this method is your Yellow tolerance. Basically, how much variation will you allow for yellow before we switch to red. If a 5% variation then it would look something like this...
=IF([% Complete]@row >= NETWORKDAYS([Start Date]@row, TODAY()) / Duration@row, "Green", IF(([% Complete]@row >= NETWORKDAYS([Start Date]@row, TODAY()) / Duration@row) - 0.05, "Yellow", "Red"))
If the row is supposed to be 50% then you will get the following outputs:
>=50% = Green
45% - 50% = Yellow
<45% = Red
To adjust the tolerable variance on the Yellow, change 0.05 to whatever percentage variance you prefer. Smartsheet formulas read percentages as a decimal, so 0.05 = 5%, 0.25 = 25%, so on and so forth.
-
Hey @Paul Newcome ,
I just tried this formula, however I ran into an error with milestones (can't divide by 0) so I modified it to an IFERROR and then put Green as the IFERROR result. I also tried modifying it to 20 percent.
Here's the new query.
=IFERROR(IF([% Complete]@row >= NETWORKDAYS([Start Date]@row, TODAY()) / Duration@row, "Green", IF(([% Complete]@row >= NETWORKDAYS([Start Date]@row, TODAY()) / Duration@row) - 0.2, "Yellow", "Red")), "Green")
Any idea what I'm doing wrong here? Maybe my understanding of the query is off based?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 464 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 60 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 40 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!