# 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:

• 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"))))

PMP Certified

[email protected]

www.mobilproject.it

• 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"))))

• 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?

PMP Certified

[email protected]

www.mobilproject.it

• 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

PMP Certified

[email protected]

www.mobilproject.it

• 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"))))

PMP Certified

[email protected]

www.mobilproject.it

• 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.

[email protected]

PMP Certified

[email protected]

www.mobilproject.it

• 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?

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!