Task Health - Red, Yellow, Green

blmccue

I am currently using =IF(TODAY() - [Due Date]@row < 0, "Green", IF(TODAY() > [Due Date]@row, "Red", IF(TODAY() - [Due Date]@row < [Processing Goal]@row, "Yellow"))) because I can't figure out how to get the yellow to work correctly.

I am looking for a formula that will display green if today is less than the due date, red if today is greater than the due date and yellow if today + 1/2 the processing goal is less than the due date. If the processing goal is 10 days, I want the health to turn yellow when we are 5 days from the due date so we can assign additional resources before we reach the due date. Can anyone help?

Paul Newcome

Give this a try:

=IF(TODAY() > [Due Date]@row, "Red", IF(TODAY(ROUND(VALUE([Processing Goal]@row) / 2)) < [Due Date]@row, "Green", "Yellow"))

If that doesn't work, how exactly is the Processing Goal column being populated?

Monique Odom-Stearn

Hello @blmccue!

Try the following formula:

=IF(TODAY() > [Due Date]@row, "Red", IF(TODAY([Processing Goal]@row / 2) < [Due Date]@row, "Green", "Yellow"))

blmccue

Hi @Monique_Odom_Comcast, I had tried that once and tried again but receive and invalid data type error.

blmccue

Wondering if anyone else has any suggestions?

I've also added a helper column that calculates 1/2 the processing goal to see if that would help fix the errors

=IF(TODAY() > [Due Date]@row, "Red", IF(TODAY(Helper@row) < [Due Date]@row, "Green", "Yellow"))

Paul Newcome

Give this a try:

=IF(TODAY() > [Due Date]@row, "Red", IF(TODAY(ROUND(VALUE([Processing Goal]@row) / 2)) < [Due Date]@row, "Green", "Yellow"))

If that doesn't work, how exactly is the Processing Goal column being populated?

DKazatsky2

Hi @blmccue,

The issue may be in trying to use a decimal inside TODAY(), that causes an "#invalid data type" error.

Hope that helps,

Dave

blmccue

@Paul Newcome, that worked!

Greatly appreciated.

Paul Newcome

Happy to help. 👍️