Task Health

RobNY2

I am having hard time to have a formula to obtain the health color for the task

I have 3 columns: End Date, Circuit Quote% Status and Quote Health

IF(AND( [Circuit Quote % Status]@row <=0.5, [End Date]@row <= Today (7)), “Red”

IF(AND([Circuit Quote % Status]@row >=0.51 , [Circuit Quote % Status]@row<=0.95, [End Date]row, <=TODAY(7)),”yellow”

Now…… IF(AND([Circuit Quote % Status]@row >=0.95, [Circuit Quote % Status]@row<=0.1, [End Date]row, <=TODAY(7)),”green”

IF Circuit Quote % Status is not 100% and finishes after the End Date stay RED

IF Circuit Quote % Status has “N/A” the health is Grey

I really appreciate any help

Thanks

Rob😀

Mike TV

@RobNY2

I got it up to this point with this formula:

=IF(AND([Circuit Quote % Status]@row <= 0.5, [End Date]@row <= TODAY(7)), "Red", IF(AND([Circuit Quote % Status]@row >= 0.51, [Circuit Quote % Status]@row <= 0.95, [End Date]@row <= TODAY(7)), "Yellow", IF(AND([Circuit Quote % Status]@row > 0.95, [Circuit Quote % Status]@row <= 1, [End Date]@row <= TODAY(7)), "Green", IF([Circuit Quote % Status]@row = "N/A", "Gray"))))

A couple things are not defined properly here. First, you've got quotation marks that are slanted. SmartSheet cannot read those. Then for your definitions you need to have the first letter capitalized or it will print text in the cell instead of a colored symbol. For the Green definitions, your post said it should have greater than 95% and less than 10% which isn't possible. So I changed that in my formula to greater than 95% and less than 100% which I'm guessing is what you intended.

The only thing I couldn't write the formula for was this "IF Circuit Quote % Status is not 100% and finishes after the End Date stay RED". There's no column for date finished so I'm not sure where you were going with that. A condition could be added for less than 100% and End Date greater than TODAY() but I'm not sure if that's what you wanted.

RobNY2

Mike

How about creating a Completion column and adding that below so you would have something to refer to to keep Health Red after the task is completed the "Due Date". What do you think?

RobNY2

Mike

Also since you are smart and create good formulas. I have another formula that is not answered and I am stuck. If you have the time, I really appreciate if you could help me 

🙏

TASK % COMPLETE FORMULA — Smartsheet Community