Health Ball Color Based on Dates and % Complete
We are utilizing the RYGB properties for our Health column and trying to improve the tracking of each task using this criteria:
- If a task has NOT started and no progress has been made, no color indicator
- If a task has started and is not late, but progress has been made, green color indicator
- If a task is 100% complete, blue color indicator
- If a task has started and is not late, but is 0% complete, yellow color indicator
- If a task has passed its due date and is less than 100% complete, red color indicator
The columns in play are Start Date, Commit Date (due date), and % Complete. I believe the health ball allocation should look like this:
- If task has NOT reached its Start Date and % Complete = 0, no health ball
- If task has reached its Start Date or not reached its Commit Date and % Complete > 0, green health ball
- If % Complete = 1, blue health ball
- If task has reached or passed its Start Date and % Complete = 0, yellow health ball
- If task has passed its Commit Date and % Complete < 1, red health ball
I have been working with this formula but it is unparseable (and incomplete since I was struggling with what I have so far):
=IF(AND([% Complete]3 < 1, TODAY() < [Start Date]3), "Green", IF([% Complete]3 = 1, "Blue", IF(AND([% Complete]3 = 0, TODAY() > [Start Date]3), "Yellow")))))
Comments
-
Try something like this...
=IF([% Complete]@row = 1, "Blue", IF([Commit Date]@row < TODAY(), "Red", IF([Start Date]@row < TODAY(), IF([% Complete]@row = 0, "Yellow", "Green"))))
-
That's close to what we're looking for but it doesn't tie the % Complete with the dates. The main goal is to track which tasks haven't started that should have along with the normal on track, late, and complete statuses.
-
IF([% Complete]@row = 1, "Blue", IF([Commit Date]@row < TODAY(), "Red", IF([Start Date]@row < TODAY(), IF([% Complete]@row = 0, "Yellow", "Green"))))
This does actually take into account the % Complete. If it is 100% (% Complete = 1), then "Blue". If it is not 1 (which means it is less than 100%), it will move on to the remainder of the formula.
.
So here's a breakdown of what this formula says...
.
IF([% Complete]@row = 1, "Blue",
If it is 100%, Blue, otherwise proceed to the next portion.
.
IF([Commit Date]@row < TODAY(), "Red",
*If it is NOT 100% complete is implied simply by making it to this stage of the formula*
If the Commit Date is in the past, "Red", otherwise move on to the next portion.
.
IF([Start Date]@row < TODAY(), IF([% Complete]@row = 0, "Yellow", "Green"
*NOT 100% complete and Commit Date is NOT in the past is implied by making it to this stage of the formula*
If the Start Date is in the past, look at the % Complete
- If it is 0% Complete, "Yellow"
- Because NOT 0% and NOT 100% are already implied by making it this far, by default anything from 1 - 99% will trigger a "Green" if the Start Date is in the past.
.
Anything else (which at this point would be either a blank row or a Start Date in the Future) would generate a blank.
.
One of the nice things about nested IF's is that if the IF makes it to the next section, every argument before that is implied to be false. Therefore we don't have to repeat it.
.
=IF(This is true, "this", IF(That is true, "that"))
By default, to get an output of "that", This HAS to be false. We don't have to say
=IF(This is true, "this", IF(AND(This is false, that is true), "that"))
It's already implied.
-
This is great! Thank you so much, especially for the explanation. Your formula works perfectly in our sheet.
I'm trying to mirror the same thing with our Status column. We have 5 status categories:
- Not Started (hasn't hit start date and 0% complete)
- In Progress (1-99% complete)
- At Risk (not 100% complete and due in 7 days)
- Late (not 100% complete and reached or passed Commit Date)
- Complete (100% complete)
We have also have 2 checkbox-initiated statuses: Descoped and Requires Attention.
I'm working with your formula above and created this:
=IF(Descoped4, "Descoped", IF([Req Attn]4, "Requires Attention", IF([% Complete]4 = 1, "Complete", IF([Commit Finish Date]4 < TODAY(), "Late", IF([Commit Start Date]4 < TODAY(), IF([% Complete]4 = 0, "Not Started", "In Progress"))))))
It works for every action except when the task has not hit its Start Date but has >0% progress. I haven't worked in the At Risk portion yet because I'm not sure where that would sit.
-
=IF(Descoped@row = 1, "Descoped", IF([Req Attn]@row = 1, "Requires Attention", IF([% Complete]@row = 1, "Complete", IF([TODAY() > [Commit Finish Date]@row, "Late", IF([% Complete]@row > 0, "In Progress", "Not Started")))))
Give something like this a try. I believe it covers all listed variations to include showing "In Progress" even if the Start Date is still in the future if the % Complete is greater than 0.
-
You had an extra bracket before the first "TODAY()" but once deleted, the formula works.
If I wanted to incorporate the "At Risk" status, where would that sit? I'm trying this and getting an "INCORRECT ARGUMENT" message:
=IF(Descoped6 = 1, "Descoped", IF([Req Attn]6 = 1, "Requires Attention", IF([% Complete]6 = 1, "Complete", IF(AND([Commit Finish Date]6 <= TODAY(7), [% Complete]6 < 1), "At Risk", "In Progress", IF(TODAY() > [Commit Finish Date]6, "Late", IF([% Complete]6 > 0, "In Progress", "Not Started"))))))
-
My apologies. I meant to incorporate the "At Risk" and ended up completely missing it while typing...
=IF(Descoped@row = 1, "Descoped", IF([Req Attn]@row = 1, "Requires Attention", IF([% Complete]@row = 1, "Complete", IF([TODAY() > [Commit Finish Date]@row, "Late", IF([Commitment Finish Date]@row > TODAY(-7), "At Risk", IF([% Complete]@row > 0, "In Progress", "Not Started"))))))
-
That looks like it's working. Thank you!
Going back to the original health ball question, I'm noticing that tasks with >0% completion prior to their Commit Start Date do not activate the green health ball. Their Status will change to "In Progress" but the health ball will change.
This is the current formula that works in all other aspects:
=IF([% Complete]15 = 1, "Blue", IF([Commit Finish Date]15 < TODAY(), "Red", IF([Commit Start Date]15 < TODAY(), IF([% Complete]15 = 0, "Yellow", "Green"))))
-
Based on your original post, I had assumed the project start was based on a date. It wasn't specified, so I didn't include it. Sorry about that. Since it can be either the start date or a % complete above 0, try this one...
=IF([% Complete]@row = 1, "Blue", IF([Commit Finish Date]@row < TODAY(), "Red", IF([Commit Start Date]@row < TODAY(), IF([% Complete]@row = 0, "Yellow", "Green"), IF([% Complete]@row > 0, "Green"))))
-
This worked! Thank you so much! I apologize for not responding sooner. I implemented your solution right after you shared it, then immediately had to update all of our reports for the board meeting this week. Thanks again...this was a tremendous help!
Help Article Resources
Categories
Check out the Formula Handbook template!