How do I show health based on end date & % complete?

I'm trying to show health of a project based on how far out from End date + how much of the work has been completed.


100% complete before or on End date


70-99% and today is < 1 range of End Date

<69% and date within 2 days of End Date


<69% and today is <1 range of End Date

This was what I wrote, and I keep getting "Unparseable"

=IF([%Complete]@row=1, "Green", IF((OR(AND[% Complete]@row<1, [%Complete]@row>.69, TODAY(-1)<[End Date]@row), (AND[%Complete]@row <.7, TODAY(-2)<[End Date]@row) ,"Yellow", "Red")

Best Answer


  • Thank you!!! I was so close! I also added a part for "blank" if not started and within a range of the Start Date, so now the formula reads as:

    =IF(AND(Status@row = "Not Started", [Start Date]@row > TODAY(1), (ISBLANK([% Complete]@row))), "", IF([% Complete]@row = 1, "Green", IF(OR(AND([% Complete]@row < 1, [% Complete]@row > 0.69, TODAY(-1) < [End Date]@row), AND([% Complete]@row < 0.7, TODAY(-2) > [End Date]@row)), "Yellow", "Red")))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!