Health of Task or Project using % Complete & Finish Date - Sanity Check

Charles Elam

I found something close to what I am looking for here:

NEED HELP: Health Formula based on start date, end date, and %100 complete needed. — Smartsheet Community

This formula doe s a great job of flagging the dates in relation to time left.

However, I am looking for a health check that can take into account both percentage complete and time left.

I am not sure I have gone about my solution the right way. I am making this post for the community to walk through what I have done as a sanity check or find potential gaps I have not accounted for. Thank you in advance for your review and input.

Phase 1: Initial Easy grab formulas

1. If % Complete = 1, Up (This means we are done)
2. If % Complete = 0 and if Today's Date >End Date, Down (This means we have not started and currently behind)

Phase 2: Helper columns

Now the next set of ideas and thinking is where I keep going in circles. I am not sure the best way to organize the data. I have three helper columns currently.

Helper Column 1 (HC1): % Progress

HC1 Reason: Manually entered in weekly/daily meetings

HC1 Formula: No formula just a fill in text/number cell

Helper Column 2 (HC2): % Delta

HC2 Reason:: This helps me calculate where Today I am in relation to the upcoming end date and puts out a percentage of time that has passed. My percentage is showing as a negative when it is a very large amount of days out from completion. So I have put an IF statement to zero it out and only start taking into account a percentage counter when the percentage is greater than zero.

HC2 Formula:

=IFERROR(IF((1 - (([End Date]@row - TODAY()) / Duration@row)) > 0, 1 - (([End Date]@row - TODAY()) / Duration@row), 0), "")

Phase 3: Logic Ranges

So I created a table in the order I would like to do a check so if you see a 1 2 3 or 4 in the FALSE portion of the statement. That is telling me where I should be copying my next formula.

You should also know that when I create long strings of statements I do them in individual cells to test the logic and then string them together in my final cell with formatting.

EXAMPLE of my Cells and Testing Logic:

Logic Check of what I am wanting:

0 - If my project is complete I'm done - Up Health

1 - If my timeline has started but I have not made any progress - Down Health

2 - If my completion is less than 80% and my time passed is greater than 90% - Down Health

3 - If my completion is less than 100% and my time has passed the delivery date - Down Health

4 - If at any time the difference of % Complete and % Delta is greater than 15% - Down Health

IFERROR was added with a "MILESTONE" tag for some durations that are going to net me a 0.

Final formula:

=IFERROR(IF([% Complete]@row >= 1, "Up", IF(AND([% Complete]@row <= 0, [% Delta]@row > 0), "Down", IF(AND([% Complete]@row < 0.8, [% Delta]@row >= 0.9), "Down", IF(AND([% Complete]@row < 1, [% Delta]@row > 1), "Down", IF(([% Delta]@row - [% Complete]@row) > 0.15, "Down", "Up"))))), "MILESTONE")

Formatting Properties:

If you have made it this far. Thanks for the read. Maybe this will be helpful to someone later on.