RYGB health changes on date and status

=IF(ISBLANK([End Date]@row), "", IF(Status@row = "Complete", "Blue", IF(NETWORKDAYS(TODAY(), [End Date]@row) < 0, "Red", IF(NETWORKDAYS(TODAY(), [Start Date]@row) <= 6, IF(Status@row = "Not Started", "Yellow", "Green")))))

Goal: Health RYGB balls.

-red, end date past due in any status

-yellow, start date <=6days of today, status not started

-blue - status is complete

-green if all is good.

Issue: the ball will not turn Green unless within 6 days of today in a status of in progress.

I've tried to reorder the criteria, but then cannot get the formula to work at all.

Any help welcome!

Find more posts tagged with

Formulas

Accepted answers

Genevieve P.

Hi @Angela.R

This is due to how your "Yellow" statement is structured. Instead of two IF statements, it looks like you actually want an AND statement in there.

For example, instead of:

IF(NETWORKDAYS(TODAY(), [Start Date]@row) <= 6, IF(Status@row = "Not Started", "Yellow",

Try:

IF(AND(NETWORKDAYS(TODAY(), [Start Date]@row) <= 6, Status@row = "Not Started"), "Yellow",

Full Formula:

=IF(ISBLANK([End Date]@row), "", IF(Status@row = "Complete", "Blue", IF(NETWORKDAYS(TODAY(), [End Date]@row) < 0, "Red", IF(AND(NETWORKDAYS(TODAY(), [Start Date]@row) <= 6, Status@row = "Not Started"), "Yellow", "Green"))))

Cheers,

Genevieve

All comments

Genevieve P.

Hi @Angela.R

This is due to how your "Yellow" statement is structured. Instead of two IF statements, it looks like you actually want an AND statement in there.

For example, instead of:

IF(NETWORKDAYS(TODAY(), [Start Date]@row) <= 6, IF(Status@row = "Not Started", "Yellow",

Try:

IF(AND(NETWORKDAYS(TODAY(), [Start Date]@row) <= 6, Status@row = "Not Started"), "Yellow",

Full Formula:

Cheers,

Genevieve

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.