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


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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!