R/Y/G/B Health Formula

Hi, trying to get my Health formula to work for the status of tasks. Got the 'Blue' for Complete part of the formula correct, however when trying to build out the rest of the formula I keep getting syntax errors. The Health definitions the formula needs to perform are as follows. Any examples greatly appreciated.

GREEN:

Status is In Progress AND

% Complete is 1% - 99% AND

Completion Date (Planned) is > 60 days in the future

YELLOW:

Status is In Progress AND

% Complete is 1% - 99% AND

Completion Date (Planned) is < 60 days in the future

RED:

Status is Not Started OR

Status is In Progress AND

% Complete is 1% - 99% AND

Completion Date (Planned) is < 30 days in the future OR

Completion Date is in the past.

Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭

    Hi Lynne,


    Try this:

    =if(Status@row="Complete","Blue",if(AND(Status@row="In Progress",[% Complete]@row>=0.01,[% Complete]@row<1,[Completion Date (Planned)]@row>TODAY(60)),"Green",if(AND(Status@row="In Progress",[% Complete]@row>=0.01,[% Complete]@row<1,[Completion Date (Planned)]@row<=TODAY(60)),"Yellow",if(Status@row="Not Started","Red",if(AND(Status@row="In Progress",[% Complete]@row>=0.01,[% Complete]@row<1,[Completion Date (Planned)]@row<TODAY(30)),"Error"))))

    I threw the "Error" part in just as a check. Let me know if it works!


    Best,

    Heather

  • Hi Heather,

    Thanks for your response. I tried and got syntax errors. However, thankfully got the formula worked out in my ProDesk session with Smartsheet and sharing the formula below:

    =IF(AND(Status@row = "Complete", [% Complete]@row = 1), "Blue", IF(AND(OR(Status@row = "Not Started", Status@row = "In Progress"), [% Complete]@row > 0, [% Complete]@row < 1, [Completion Date (Planned)]@row < TODAY(30)), "Red", IF(AND(Status@row = "In Progress", [% Complete]@row > 0, [% Complete]@row < 1, [Completion Date (Planned)]@row < TODAY(60)), "Yellow", IF(AND(Status@row = "In Progress", [% Complete]@row > 0, [% Complete]@row < 1, [Completion Date (Planned)]@row >= TODAY(60)), "Green", IF(Status@row = "Not Started", "Red")))))

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭

    @Lynne Jeffries , so glad you got it ironed out!


    Best,

    Heather

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!