Help with formula with multiple conditions to auto-update RYG ball

I am trying to create a formula with multiple conditions in order to automatically change the color of the RYG ball.

IF the Status = Not Started/In Progress/Blank AND the End Date is less than TODAY () then the ball should be Red

OR IF the Status = Not Started/In Progress/Blank AND the End Date is in the next 7 days then the ball should be Yellow

otherwise the ball should be Green

I currently have this formula =IFERROR(IF([End Date]@row < TODAY(), "Red", IF([End Date]@row < TODAY(+7), "Yellow", "Green")), " ") but I just need to add other conditions for Red & Yellow status.

Thanks in advance for any help!

Find more posts tagged with

Accepted answers

Paul Newcome

Try something like this...

=IFERROR(IF(OR(Status@row = "Not Started", Status@row = "In Progress", Status@row = ""), IF([End Date]@row < TODAY(), "Red", IF([End Date]@row < TODAY(7), "Yellow", "Green")), ""), "")

All comments

Paul Newcome

Try something like this...

=IFERROR(IF(OR(Status@row = "Not Started", Status@row = "In Progress", Status@row = ""), IF([End Date]@row < TODAY(), "Red", IF([End Date]@row < TODAY(7), "Yellow", "Green")), ""), "")

Jen Garijanian

That works - thank you Paul!

Paul Newcome

Happy to help. 👍️

Jen Garijanian

@Paul Newcome this formula works great but when my Status field is blank (no End Date entered) I want the default of my RYG to be blank. Is there a way to add that into the existing formula you helped me with?

Paul Newcome

Yes. Your initial post had a "blank status" included as one of the possible options for generating a color. If you want it to be left blank, then we should be able to just remove that particular bit from the original formula like so:

=IFERROR(IF(OR(Status@row = "Not Started", Status@row = "In Progress"), IF([End Date]@row < TODAY(), "Red", IF([End Date]@row < TODAY(7), "Yellow", "Green")), ""), "")

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.