Automatic RYGB Lights

Hi All,

Currently I'm trying to make my RYGB column give the right color light when it meets certain criteria. This must be based on a Start Date column, End Date column and a status column, but I can't seem to figure out a formula that works.

So the way it should work is like below:

Is there anyone who might know a solution? 😃

Thanks in advance!

Find more posts tagged with

Sheets

Formulas

Accepted answers

Emilio Wright

@Jeffrey Wagemans I created the formula based on your table, although it has gaps and you will begin to notice them when users begin to use the fields. I found one but have not changed the formula since you provided the needed information.

=IF(Status@row = "not started", IF(OR(ISBLANK([Start Date]@row), ISBLANK([End Date]@row)), "Blue", IF([Start Date]@row > TODAY(), "Blue", "Red")), IF(Status@row = "in progress", IF([End Date]@row < TODAY(), "Red", "Yellow"), IF(AND(Status@row = "Completed", [End Date]@row < TODAY()), "Green", IF(Status@row = "Canceled", "Blue", "Blue"))))

I created a formula similar to this on my Project Plans. I opted by not caring what the user had on the Status field unless it was something critical like "At Risk" or "Abandoned" or "Completed". I simply used the Date fields to guide my Status. You can kind of gauge what status it should be based on the dates.

This is a snippet of the formula but I think it conveys the message. Don't mind the parenthesis as I simply cut this from my formula without checking if the parentheses have pairs.

IF(AND(NOT(ISBLANK([Start Date]#)), NOT(ISBLANK([End Date]#))), IF(AND([Start Date]# <= TODAY(), [End Date]# >= TODAY()), "In Progress", IF(AND([Start Date]# <= TODAY(), [End Date]# < TODAY()), "Completed", "Planned Not Started")), "Unplanned"))

All comments

Emilio Wright

@Jeffrey Wagemans I can help you make the formula, it would be quite a few nested IF statements. Normally I would take the column that has the most options per group as the initial criteria since it will result in fewer IF statements.

Emilio Wright

This is a snippet of the formula but I think it conveys the message. Don't mind the parenthesis as I simply cut this from my formula without checking if the parentheses have pairs.

Jeffrey Wagemans

Hi Emilio!

Thank you so much, it worked!

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.