Trying to format the RYG balls according to Status and Due Date

rbuckner

I have formatted my RYG balls to change according to the status of the task; Not Started = Grey, In Progress = Yellow, Complete = Green, and so on. I want Red = Not completed, and also past due.

This is what I have:

=IF(Status@row = "Not Started", "Gray", IF(Status@row = "In Progress", "Yellow", IF(Status@row = "Complete", "Green", IF(AND(today@row - [Due Date]@row >= 0, Status@row <> "Complete", "Red")))))

I have tried several permutations of this, this is just the most recent one.

Paul Newcome

That goes back to the order your IF statements are in. Once it hits a true value, it stops calculating. Even with having "<> "Complete" as part of the AND, because it comes after the "In Progress" portion it still would not change to "Red". If the status is "In Progress", it will stop at the first true value's output which is "Yellow".

Here it is a little rearranged...

IF(Status@row = "Complete", "Green", IF(today@row - [Due Date]@row >= 0, "Red", IF(Status@row = "Not Started", "Gray", IF(Status@row = "In Progress", "Yellow"))))

If all that is in "today@row" is today's date, you can also replace that portion with TODAY().

IF(Status@row = "Complete", "Green", IF(TODAY() - [Due Date]@row >= 0, "Red", IF(Status@row = "Not Started", "Gray", IF(Status@row = "In Progress", "Yellow"))))

Paul Newcome

Happy to help! 👍️

Paul Newcome

You are actually VERY close.

In your original formula, move one of those closed parenthesis from the end to before the comma right before "Red".

=IF(Status@row = "Not Started", "Gray", IF(Status@row = "In Progress", "Yellow", IF(Status@row = "Complete", "Green", IF(AND(today@row - [Due Date]@row >= 0, Status@row <> "Complete"), "Red"))))

However... You actually don't need the AND function. Because you have logic for if the status is "Complete", anything that runs after that automatically assumes that the status is not complete. Once a nested IF finds a true value, it will not run anything after it, so it is implied that all previous IF's are false.

=IF(Status@row = "Not Started", "Gray", IF(Status@row = "In Progress", "Yellow", IF(Status@row = "Complete", "Green", IF(today@row - [Due Date]@row >= 0, "Red"))))

Also... What exactly is in "today@row"?

rbuckner

Paul,

thank you for your help, but it doesn't quite seem to have solved my problem.

I input the corrections, and all it will do is make the RYG ball "red" in empty rows. But I was hoping that using <>"Complete" would mean that if the value were anything other than "Complete", that the ball would be Red once the due date has passed. With that first correction you gave me, I have a row with the status as "In Progress" and the due date has passed, but the ball remains yellow rather than becoming red.

Also, the today@row was something I tried when I couldn't get it to work, I thought if I subtracted that value from the due date column value, it would work better than using ...TODAY()

Paul Newcome

That goes back to the order your IF statements are in. Once it hits a true value, it stops calculating. Even with having "<> "Complete" as part of the AND, because it comes after the "In Progress" portion it still would not change to "Red". If the status is "In Progress", it will stop at the first true value's output which is "Yellow".

Here it is a little rearranged...

IF(Status@row = "Complete", "Green", IF(today@row - [Due Date]@row >= 0, "Red", IF(Status@row = "Not Started", "Gray", IF(Status@row = "In Progress", "Yellow"))))

If all that is in "today@row" is today's date, you can also replace that portion with TODAY().

IF(Status@row = "Complete", "Green", IF(TODAY() - [Due Date]@row >= 0, "Red", IF(Status@row = "Not Started", "Gray", IF(Status@row = "In Progress", "Yellow"))))

rbuckner

Awesome, that works!

Thank you!

Paul Newcome

Happy to help! 👍️