Simple task health formula issue

edelente

I am using this formula that I adapted to my comun names from one that was posted here elsewhere.

=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" or "Yellow" are the root cause. (The 'Duration Complete' is just there to compare % Complete to how much of the planned schedule for that task has been consumed. Maybe there is a better way to just compare those values?

EG:

What am I doing wrong? Thanks so much for any help anyone might offer.

mreed

Hi EG, I looks like you only rounded the duration calculation for green, but not for yellow or red. Those are returning invalid data types because TODAY() cannot accept a decimal, and the durations in those rows are odd.

I also think your formula might not be doing what you want it to, as you have a row that's only 50% completed after 100% of the duration, and it is returning a green status; I think you want it to be red. All of your Today formulas are currently looking for future dates -- to look for previous dates to compare to start date, you'd have to multiply the rounded calculation by -1.

Since you have a calculation column for the duration %, would it accomplish the same goal and simplify your formula to say:

=IF([Duration Percent]@row = 0, "Blue", IF([Duration Percent]@row <= [% Complete]@row, "Green", IF(AND([Duration Percent]@row >= 0.5, [% Complete]@row < 0.5), "Yellow", IF(AND([Duration Percent]@row > 0.75, [% Complete]@row < 0.75), "Red"))))

mreed

Hi EG, I looks like you only rounded the duration calculation for green, but not for yellow or red. Those are returning invalid data types because TODAY() cannot accept a decimal, and the durations in those rows are odd.

I also think your formula might not be doing what you want it to, as you have a row that's only 50% completed after 100% of the duration, and it is returning a green status; I think you want it to be red. All of your Today formulas are currently looking for future dates -- to look for previous dates to compare to start date, you'd have to multiply the rounded calculation by -1.

Since you have a calculation column for the duration %, would it accomplish the same goal and simplify your formula to say:

=IF([Duration Percent]@row = 0, "Blue", IF([Duration Percent]@row <= [% Complete]@row, "Green", IF(AND([Duration Percent]@row >= 0.5, [% Complete]@row < 0.5), "Yellow", IF(AND([Duration Percent]@row > 0.75, [% Complete]@row < 0.75), "Red"))))