Use date and status to define RYG balls

Anna Keeton

Hey all,

Trying to make a simple task management sheet, using today's date and the task status to define a Red, Yellow, Green or Grey ball.

If end date is today or later, AND status is not complete or cancelled, green

If end date is today -1, AND status is not complete or cancelled, yellow

If end date is today -2 or further in the past, AND status is not complete or cancelled, red

If status is complete or cancelled, grey

I'm assuming I need to combine IF OR AND functions... but, not getting very far. Your help would be amazing!! Here's what I have at the moment....

=IF(OR(AND(Status@row <> "Complete", [End Date]@row = TODAY(), "Yellow")))

SoS | Dan Palenchar

This formula will do it

=IF(Status@row = "Complete", "Gray", IF(Status@row = "Cancelled", "Cancelled", IF(TODAY() = [End Date]@row, "Green", IF([End Date]@row - TODAY() = -1, "Yellow", IF([End Date]@row - TODAY() = -2, "Red", "N/A")))))

You can check for Complete and the Cancelled Status to start, thereby eliminating the need to use the AND/OR Statements. This is because as soon as the first statement is met nothing else in the formula triggers.

Note that as described:

• I wrote the formula to display "Cancelled" in the RAG column for Cancelled jobs. This could be any text you like or it could be empty by using two quotes (""),
• Statuses of "Not Started," "On Hold", and "In Progress" will behave the same,
• There is no status associated with any non-Completed/Cancelled task that has an end date in the future.

SoS | Dan Palenchar

This formula will do it

=IF(Status@row = "Complete", "Gray", IF(Status@row = "Cancelled", "Cancelled", IF(TODAY() = [End Date]@row, "Green", IF([End Date]@row - TODAY() = -1, "Yellow", IF([End Date]@row - TODAY() = -2, "Red", "N/A")))))

You can check for Complete and the Cancelled Status to start, thereby eliminating the need to use the AND/OR Statements. This is because as soon as the first statement is met nothing else in the formula triggers.

Note that as described:

• I wrote the formula to display "Cancelled" in the RAG column for Cancelled jobs. This could be any text you like or it could be empty by using two quotes (""),
• Statuses of "Not Started," "On Hold", and "In Progress" will behave the same,
• There is no status associated with any non-Completed/Cancelled task that has an end date in the future.

Anna Keeton

Dan, this is AMAZING! Thanks a lot :)

I wonder if I can go one step further.... I'd like yellow to show for 1 to 4 days delay, and red to show for 5 days plus delay. And ideally, I'd like that to be working days. Is that possible??

I added < 0 and < -4 - and it seems to work for yellow, but not for red... any clue @Dan Palenchar ?

=IF(Status@row = "Complete", "Gray", IF(Status@row = "Cancelled", "Cancelled", IF(TODAY() = [End Date]@row, "Green", IF([End Date]@row - TODAY() < 0, "Yellow", IF([End Date]@row - TODAY() < -4, "Red", "N/A")))))