Health formula from Status

Hi all. I'm new and trying to tweak a health column formula. We've decided to remove our % Complete column which we'd originally used.

Original Formula:

=IF(AND([Due Date]@row < TODAY(), NOT(ISBLANK([Due Date]@row)), OR([% Complete]@row <> 1, ISBLANK([% Complete]@row))), "Red", IF(AND(NOT(ISBLANK([Due Date]@row)), OR([% Complete]@row = 0, ISBLANK([% Complete]@row))), "Gray", IF(AND(([% Complete]@row < 0.5), [Network Days]@row <= 2, NOT(ISBLANK([Due Date]@row))), "Yellow", IF(NOT(ISBLANK([Due Date]@row)), "Green ", " "))))

I'd like to use a simple logic such as:

Past due date and NOT complete = Red

Not started = Gray

In progress = Green

Blocked = Yellow

Can anyone please recommend a formula that would work?

Thank you!

Find more posts tagged with

Formulas

Accepted answers

All comments

Mark Cronk

Hi @Christine Brown (SAI) ,

I assumed your comlplete was a checkbox and you previous "yellow" formula was the criteria you're using for Blocked. if not, you'll need to adjust. Try:

=IF(OR([start date]@row>=today(), ISBLANK([start date]@row)), "Gray", IF(AND([due date]@row<Today(), complete@row=0), "Red", IF(AND([Network Days]@row <= 2, NOT(ISBLANK([Due Date]@row)), "Yellow", "Green")))

Help?

Mark

Christine Brown (SAI)

Thank you @Mark Cronk ! I'm still getting a syntax error unfortunately. I think we don't want Yellow/Blocked associated with the date, just status change. We currently had a green strikethrough for complete items.

I will continue to tinker. Thanks again!

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.