RYGB Based on two date columns

Hi All,

I have a formula that I can't get right and looking for help with the following:

Green Status = If End Date is in the future and Status is "In Progress"
Yellow Status = If Start Date has begun and the Status is "To Do", Backlog", or "Planning"

Red Status = If End Date has passed and the status is "In Progress" or "On Hold"; If project has no Start Date OR End Date and status is "In Progress" or "On Hold"

Blue Status = If there is no start date and the status is in "To Do", or "Backlog"

Any help would be appreciated, thank you!

Find more posts tagged with

Accepted answers

Gia Thinh

Hi @MMorgan

Try this formula:

=IF(AND([End Date]@row > TODAY(), Status@row = "In Progress"), "Green", IF(AND(AND([Start Date]@row <> "", [Start Date]@row <= TODAY()), OR(Status@row = "To Do", Status@row = "Backlog", Status@row = "Planning")), "Yellow", IF(AND([End Date]@row < TODAY(), OR(Status@row = "In Progress", Status@row = "On Hold")), "Red", IF(AND(OR([End Date]@row = "", [Start Date]@row = ""), OR(Status@row = "In Progress", Status@row = "On Hold")), "Red", IF(AND([Start Date]@row = "", OR(Status@row = "To Do", Status@row = "Backlog")), "Blue")))))

Hope it works for you.

MMorgan

@Gia Thinh

This is great! It works! Thank you so much

All comments

Gia Thinh

Hi @MMorgan

Try this formula:

Hope it works for you.

MMorgan

This is great! Thank you @Gia Thinh. I just ran a test and all items work, except for On Hold when the end date is in the future. Can you possibly add if it is on hold but with a future end date, the status is green. @https://giathinh.tech/wegrid/

Again, i really appreciate this!

Gia Thinh

@MMorgan

Try this updated formula:

=IF(AND([End Date]@row > TODAY(), OR(Status@row = "In Progress", Status@row = "On Hold")), "Green", IF(AND(AND([Start Date]@row <> "", [Start Date]@row <= TODAY()), OR(Status@row = "To Do", Status@row = "Backlog", Status@row = "Planning")), "Yellow", IF(AND([End Date]@row < TODAY(), OR(Status@row = "In Progress", Status@row = "On Hold")), "Red", IF(AND(OR([End Date]@row = "", [Start Date]@row = ""), OR(Status@row = "In Progress", Status@row = "On Hold")), "Red", IF(AND([Start Date]@row = "", OR(Status@row = "To Do", Status@row = "Backlog")), "Blue")))))

MMorgan

@Gia Thinh

This is great! It works! Thank you so much

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