RYG "Yellow" Error for Health Metrics in Summary Row


Hi Community,

I am trying to create an overall health metric for a project based on the information in the summary row of the schedule. The criteria is a mix of fields:

  • If the Status is on Hold = Blue
  • If the Status is Complete = Blank
  • If the Finish date has passed = Red
  • If Today is within 7 days of the Finish Date and % Complete is less than 0.75 = Yellow

These are my columns:

And I'm using this formula.

This ALMOST does what I want it to, except the "Yellow" classification is off. I'm not sure how to make it measure between two dates. It is showing that my Health is yellow because the Finish Date is greater than 7 days and less 75% but I want it to only show yellow if its within those 7 days. Help!

This might be a stretch but is there a way to make that window of time proportionate to the duration of the project? For example, if it is within 75% of the total duration and less than 75% complete, then yellow?

Thank you in advance!


Best Answer


  • Tamara Cook
    Tamara Cook ✭✭✭

    This worked beautifully Aravind. Thank you so much!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!