Automate RYG Balls Based on Two Different Dates

I am trying to create a formula that looks at two different start dates to set the project status. It looks at the first start date to see how far out (TODAY) is from that date and looks to see if there is a date in the second start date. This is what I have so far:

=IF(AND([Start Date]17 >= TODAY(+28), ISBLANK([Start Date]18)), "Green"), IF(AND([Start Date]17 <=TODAY (+28), ISBLANK([Start Date]18)), "Yellow"), IF(AND([Start Date]17 <=TODAY (+14), ISBLANK([Start Date]18)), "Red"

I also need to set a condition that if there is a date in [Start Date]18 then the project status changes to "Blue".

Any help would be appreciated. Thank you!

Find more posts tagged with

Formulas

Accepted answers

Mark Cronk

Try:

=IFERROR(IF(ISDATE( [start date]18), "Blue", IF([ Start Date]17 >= TODAY(+28), "Green", IF([Start Date]17 >=TODAY (+14), "Yellow", "Red"))),"")

If [start date]18 is a date then it responds "blue" so there's no need to check that cell in the rest of the formula. I simplified your logic since IF statements go in order until they find a true response.

Work?

Mark

All comments

Mark Cronk

Try:

=IFERROR(IF(ISDATE( [start date]18), "Blue", IF([ Start Date]17 >= TODAY(+28), "Green", IF([Start Date]17 >=TODAY (+14), "Yellow", "Red"))),"")

Work?

Mark

Hannah Bell

Mark - that did work! Thank you for your help!

Mark Cronk

Hi Hannah, Happy to help. Glad you found a solution. Thank you for contributing to the Community.

Mark

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.