Schedule Health from End Dates and % Complete


I am currently trying to create a formula for my schedule health based off the red, yellow, and green symbols. I want green health to be if the % complete is 100% or the [End Date]@row > TODAY(). I want the red health to be [End Date]@row < TODAY() or if the % complete is less than 100%. I am not worried about the yellow symbol currently, but I would like to use this group of stoplight symbols.

The Current formula I have built is =IF(TODAY() > [End Date]@row, "Red", IF(TODAY() < [End Date]@row, "Green")). This has been working for the dates but does not account for the % complete. I also tried this formula to no avail: =IF(OR([% Complete]@row = 1, [End Date]@row > TODAY()) "Green", IF([End Date]@row < TODAY(), "Red")).

Does someone know what I am doing wrong or if there is a better formula for this problem?

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!