Project Health Formula Help!

I am trying to add a column to my project plan to track the health of the lines in my plan based on the details in the % complete and due date columns. I would like to use the red, yellow and green symbols in the formula. Are there any formula's that you can recommend for tracking project health? I think the formula I am trying to build is too complicated as it is not capturing all of the components. I can build the formula to capture pieces of the logic for either the red, yellow or green, but when I attempt to combine them all together I get an error message. Below is what I would like to capture, any insight is greatly appreciated.

Need help with the line health formula.

o   Green

·        If 100%

·        If due date is more than a week in the future

o   Yellow

·        If due date is less than a week out and % complete is 99% - 50%

o   Red

·        If due date is less than a week out and % complete is 49% - 0%

·        If due date is in the past and % complete is less than 100%

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!