Risk Status- Grey, Red, Yellow, Green Bubble Formula

I'm not sure why my formula isn't working. I also wonder if I can do Today(-7) in the formula. I've seen +days but not -days so maybe that's the issue? If so, how do I write it such that if the finish date is within the next 7 days, the bubble will be yellow? Here's the formula:

=IF(status@row = "Not Started", IF(start@row< TODAY, "Grey",

IF(status@row = "not started", IF(finish@row>= TODAY, "Red",

IF(status@row = "not started", IF(start@row> TODAY, "Red",

IF(status@row= "in progress", IF(finish@row>TODAY, "Red",

If(status@row="In progress", IF(finish@row >TODAY(-7), "Yellow"),

IF(status@row= "Complete", "Green"))))))

Find more posts tagged with

Formulas

Project Management

Accepted answers

All comments

ChloeSmith

Hello,

I'm not sure if this is what you were looking for, but I was able to get the formula below to work. The only concern is if there are instances where two phrases could be correct (but result in different outputs), such as if the Status is Not Started, the Start row is less than today, and the Finish row is greater than or equal to today. That would result in a red and a grey bubble, which I don't think it can have both.

=IF(AND(Status@row = "Not Started", Start@row < TODAY()), "Gray", IF(AND(Status@row = "Not Started", Finish@row >= TODAY()), "Red", IF(AND(Status@row = "Not Started", Start@row > TODAY()), "Red", IF(AND(Status@row = "in progress", Finish@row > TODAY()), "Red", IF(AND(Status@row = "In Progress", Finish@row <= TODAY(7)), "Yellow", IF(Status@row = "complete", "Green"))))))

One other formula that I tried that I think helps mitigate having two outputs for similar inputs is below:

=IF(AND(Status@row = "Not Started", Start@row > TODAY()), "Gray", IF(AND(Status@row = "Not Started", Finish@row <= TODAY()), "Red", IF(AND(Status@row = "Not Started", Start@row < TODAY()), "Red", IF(AND(Status@row = "In Progress", Finish@row < TODAY()), "Red", IF(AND(Status@row = "In Progress", Finish@row <= TODAY(7)), "Yellow", IF(Status@row = "Complete", "Green"))))))

Finally, I did wonder if this is something you could use conditional formatting for instead of a formula since you can set those to have multiple criteria and highlight whichever column you'd want it to highlight. That could also fix the above issue for multiple colors, since you could have different columns highlighted for different things.

Hopefully, that helps!

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.