Use date and status to define RYG balls

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

Find more posts tagged with

Sheets

Formulas

Support

Project Management

Accepted answers

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.

All comments

SoS | Dan Palenchar

This formula will do it

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.