# Need Help with Task Health Formula

Hi - I'm trying to write a task health formula with the following criteria and am hoping someone can help. Here's what I'd like it to calculate:

• BLUE (NOT STARTED): If the start date is in the future and if the % complete is 0
• GREEN (ON TRACK): If today is less than 50% of the task duration and % complete is more than 50%
• YELLOW (AT RISK): If today is more than 50% of the task duration and % complete is less than 50%
• RED (OFF TRACK): If today is more than 75% of the task duration and % complete is less than 75%

Tags:

• ✭✭✭✭✭✭
edited 02/24/21

Hi @Christa Johnson

Hope you are fine, please try the following formula and convert it to a column formula.

=IF(AND(Status@row = "NOT STARTED", [start date]@row > TODAY(), [% complete]@row = 0), "Blue", IF(AND(Status@row = "ON TRACK", [start date]@row < TODAY(ROUND(duration@row / 2)), [% complete]@row >= 0.5), "Green", IF(AND(Status@row = "AT RISK", [start date]@row > TODAY(duration@row * 0.5), [% complete]@row < 0.5), "Yellow", IF(AND(Status@row = "OFF TRACK", [start date]@row > TODAY(duration@row * 0.75), [% complete]@row < 0.75), "Red"))))

bassam.khalil2009@gmail.com

• Thanks Bassam! This worked on one of the tasks where I tested the Blue status, but for all others the status is showing as blank. Any ideas why?

• Update: I adjusted the formula to remove the "ON TRACK", "AT RISK" etc from this formula since those aren't values in my table. This now works for Blue and Green, but giving the error of INVALID DATA TYPE for yellow and red examples.

=IF(AND([start date]@row > TODAY(), [% complete]@row = 0), "Blue", IF(AND([start date]@row < TODAY(ROUND(duration@row / 2)), [% complete]@row >= 0.5), "Green", IF(AND([start date]@row > TODAY(duration@row * 0.5), [% complete]@row < 0.5), "Yellow", IF(AND([start date]@row > TODAY(duration@row * 0.75), [% complete]@row < 0.75), "Red"))))

• ✭✭✭✭✭✭
edited 02/24/21

could you share a screenshot for your sheet ( please hide any sensitive date ) may be the column name or the spilling of criteria the following is a screenshot for the sample i tested?

bassam.khalil2009@gmail.com

• Here you go!

• ✭✭✭✭✭✭

the problem in the duration column because you add the letter "d", you need to extract the number of days from the duration because if you multiply 0.5*duration (44d) for example the result will be invalid

bassam.khalil2009@gmail.com

• Thanks Bassam. Hmmm..wondering how I can get around this because the duration column I am using is the default duration format provided by Smartsheet.

• ✭✭✭✭✭✭

we need to do the following change to the formula.

=IF(AND(Status@row = "NOT STARTED", [start date]@row > TODAY(), [% complete]@row = 0), "Blue", IF(AND(Status@row = "ON TRACK", [start date]@row < TODAY(ROUND(([End date]@row - [start date]@row) * 0.5)), [% complete]@row >= 0.5), "Green", IF(AND(Status@row = "AT RISK", [start date]@row > TODAY(([End date]@row - [start date]@row) * 0.5), [% complete]@row < 0.5), "Yellow", IF(AND(Status@row = "OFF TRACK", [start date]@row > TODAY(([End date]@row - [start date]@row) * 0.75), [% complete]@row < 0.75), "Red"))))

bassam.khalil2009@gmail.com

• Ugh, it's still giving me "Invalid Data Type"

• ✭✭✭✭✭✭

if you can export your sheet to excel ( keep sample data and remove any sensitive date ) and send it to my Email maybe i will prepare the exact formula using your column name and your data criteria, please list the 4 type so i can test.

bassam.k@mobilproject.it

bassam.khalil2009@gmail.com

• Thank you! I would be so grateful!

• Further update to this - I've mapped out my desired formula a bit more. Progress, but not yet yielding the right values.

Blue is showing as Blue, Green is showing as Green, Yellow is blank, Red states # Invalid Operation, Completed shows Green.

Here's what I have created if it's of help:

• ✭✭✭

What was the final outcome of this? I am using this formula from above:

=IF(AND(Start@row > TODAY(), [% Complete]@row = 0), "Blue", IF(AND(Start@row < TODAY(ROUND(Duration@row / 2)), [% Complete]@row >= 0.5), "Green", IF(AND(Start@row > TODAY(Duration@row * 0.5), [% Complete]@row < 0.5), "Yellow", IF(AND(Start@row > TODAY(Duration@row * 0.75), [% Complete]@row < 0.75), "Red"))))=

and it works for most rows but I get #INVALID DATA TYPE for some rows but not able to determine what's going on with those rows, but it seems like the rows that should be "Red" are the root cause.

EG:

What am I doing wrong?

• Hello, I am also not able to make this work in my smartsheet. @Bassam Khalil - any chance I can email you my excel too? I tried to follow the above but I am getting errors and I cannot figure it out..

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!