Health Status Based on % Complete

Lauren T

Hi,

I am trying to nail down my formula so that Health (RGYG balls) will be a reflection of % complete AND due date. I have most of the formula there, however, if a date field is missing or a task has not started without a date field, it comes up as Red. I would like it to show Gray if a task hasn't started OR/AND a Due Date has not been populated with a date yet. Is this the correct way to associate health of a project - how far along a project should be at a certain point in time?

What is missing in my formula?:

=IF(AND([Start Date]@row > TODAY(), Status@row = "Not Started"), "Gray", IF(OR(AND([% Complete]@row <= 0.25, [Due Date]@row >= TODAY(), [Due Date]@row <= TODAY(7)), AND([% Complete]@row <> 1, [Due Date]@row < TODAY())), "Red", IF(AND([% Complete]@row <= 0.5, [Due Date]@row >= TODAY(), [Due Date]@row <= TODAY(7)), "Yellow", IF(AND([% Complete]@row >= 0.75, [Due Date]@row >= TODAY(), [Due Date]@row <= TODAY(7)), "Green", "Green"))))

Thanks for any help,

Lauren

Paul Newcome

My apologies. [End Date] should be [Due Date]

Paul Newcome

I am not sure what you mean by "task hasn't started OR/AND a Due Date has not been populated". If a task is "Not Started", but the Due Date is populated, do you want "Grey"? What if the Due Date is in the past, but it is still marked as "Not Started"? Can you be a little more specific/detailed with the criteria for "Grey"?

Lauren T

Hi Paul,

Thank you for the quick reply. I thought I had in the formula that if the start date/due date was in the past it would be red automatically, perhaps I didn't. I want Gray to indicated a task that has not started yet. However, if the start date/end date is in the past, it would be Red. What's happening now is everything that hasn't started is turning up Red. What I'd like to see is, if the start date/end date is not populated or the task has not started with a start date/end date in the future to the ball would be Gray. Does that help clarify at all?

Lauren

Paul Newcome

It does. Thanks!

=IF(OR([Start Date]@row = "", [Start Date]@row > TODAY(), [End Date]@row = ""), "Grey",

Take a look at the above. Does this cover what you are trying to accomplish for "Grey"? If so, you would just drop that into the beginning of your formula.

=IF(OR([Start Date]@row = "", [Start Date]@row > TODAY(), [End Date]@row = ""), "Grey", IF(AND([Start Date]@row > TODAY(), Status@row = "Not Started"), "Gray", IF(OR(AND([% Complete]@row <= 0.25, [Due Date]@row >= TODAY(), [Due Date]@row <= TODAY(7)), AND([% Complete]@row <> 1, [Due Date]@row < TODAY())), "Red", IF(AND([% Complete]@row <= 0.5, [Due Date]@row >= TODAY(), [Due Date]@row <= TODAY(7)), "Yellow", IF(AND([% Complete]@row >= 0.75, [Due Date]@row >= TODAY(), [Due Date]@row <= TODAY(7)), "Green", "Green")))))

Lauren T

Thank Paul!

That however is coming back as Unparseable. Nothing has changed on my sheet or from my original formula.

Paul Newcome

My apologies. [End Date] should be [Due Date]