Need Help with Task Health Formula

Christa Johnson

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%

Thanks in advance for your formula wisdom!

Bassam Khalil

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

Christa Johnson

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?

Christa Johnson

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

Bassam Khalil

@Christa Johnson

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?

Christa Johnson

Here you go!

Bassam Khalil

@Christa Johnson 

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

Christa Johnson

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.

Bassam Khalil

@Christa Johnson 

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

Christa Johnson

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

Bassam Khalil

@Christa Johnson

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

Christa Johnson

Thank you! I would be so grateful!

Here's a spreadsheet

Health Status Tests.xlsx

Christa Johnson

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:

edelente

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?

Harley Esguerra

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