R/Y/G/B Health Formula

Lynne Jeffries

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.

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

Lynne Jeffries

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

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

Best,

Heather