Help with a status health formula please

Hi All, we have developed a fairly basic formula to determine the health of tasks:

=IF(Status@row = "Complete", "Green", IF(Status@row = "Canceled", "Green", IF(Status@row = "In Progress", "Yellow", IF(AND(Status@row = "Not Started", [End Date]@row <= TODAY()), "Red", IF(AND(Status@row = "On Hold", [End Date]@row <= TODAY()), "Red")))))

I would really appreciate assistance with making it smarter based on these scenarios:

If Status@row = “In Progress” and [End Date]@row < TODAY (end date in the past), show red
If Status@row = “Not Started” and [End Date]@row < TODAY (end date in the past), show red
If Status@row = “Not Started” and [Start Date]@row < TODAY (start date in the past), show yellow
If Status@row = “In Progress” and [Start Date]@row >= TODAY (start date is today or in the future), show green (currently showing yellow)

Thank you.

Find more posts tagged with

Formulas

Accepted answers

All comments

Carson Penticuff

This should function correctly; I have it working in a test sheet. However, there are a couple of disclaimers:

First, this could definitely be made more concise. Because you have some somewhat complex scenarios, I opted for human-readability at the expense of having a longer formula. Some conditions could definitely be combined to optimize this, but it would be slightly more difficult to follow the logic. (at least in my opinion)

Second, I had to make a few assumptions. For example, you do not specify the desired output if Status@row is "On Hold" and [End Date]@row is in the future. I assumed green in this scenario, but I could definitely see the logic in desiring a "yellow" result.

Give it a try.... it can always be tweaked if needed.

Danielle Arteaga

Not sure if you can see @Carson Penticuff 's formula and I can't for some reason, but - just in case - here's one that seems to work:

=IF(OR(Status@row = "Complete", Status@row = "Canceled"), "Green", IF(AND(Status@row = "In Progress", [End Date]@row < TODAY()), "Red", IF(AND(Status@row = "In Progress", [End Date]@row >= TODAY()), "Green", IF(AND(Status@row = "Not Started", [End Date]@row < TODAY()), "Red", IF(AND(Status@row = "Not Started", [End Date]@row >= TODAY()), "Green", "Yellow")))))

As Carson noted, not clear what you want for "On Hold" projects, but I made them yellow (they get caught by the value_if_false portion of the formula).

You could probably condense the "In Progress" and "Not Started" portions of this formula with OR statements, but I agree that human readability may be better here - especially if you need to later edit this.

Carson Penticuff

@Danielle Arteaga Maybe I missed a copy/paste somehow? I appear to have completely left out the formula... that is somewhat embarrassing. Anyway, here is what I meant to post:

=IF(OR(Status@row = "Complete", Status@row = "Canceled", AND(Status@row = "In Progress", [Start Date]@row >= TODAY()), AND(Status@row = "Not Started", [Start Date]@row >= TODAY()), AND(Status@row = "On Hold", [End Date]@row > TODAY())), "Green", IF(AND(Status@row = "Not Started", [Start Date]@row < TODAY(), [End Date]@row >= TODAY()), "Yellow", IF(OR(AND(Status@row = "On Hold", [End Date]@row <= TODAY()), AND(Status@row = "In Progress", [End Date]@row < TODAY()), AND(Status@row = "Not Started", [End Date]@row < TODAY())), "Red", "")))

Danielle Arteaga

No worries! I thought it was my browser just not rendering what you added.

Now @Nicole Ross has multiple ways to get where she's going. Can never hurt. 😁

Nicole Ross

Thank you so very much Danielle and Carson. Your assumptions, comments and formulas are enormously helpful!

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