Yellow if, Red if

Hello - looking for some assistance on a formula that I'm trying to create that will return my task health as Yellow if 1 - 13 days beyond target completion date, and Red if 14 days beyond. I have created the below, which works, however I'm stumped on where I need to incorporate the IF formula for Yellow, and how to ensure they are both active:

=IF(ISBLANK(Status@row), "", IF(AND([Target/Actual Completion Date]@row < TODAY(+14), Status@row <> "Complete"), "Red", IF(Status@row = "Not Started", "-", IF(Status@row = "In Process", "Yellow", IF(Status@row = "Complete", "Green")))))

I've tried the below, and it works, but it will only report health as Red regardless of how many days over the task is:

Find more posts tagged with

Conditional Formatting

Accepted answers

All comments

ro.fei

Hey @ZDevier ! It looks like it's only returning red because all you're checking for is if the Target/Actual Completion Date is less than 14 Days after the current date. I believe you may want to switch your less than sign to a greater than sign. If that doesn't work, I would try using the NETDAYS function to set your parameters.

Hope this helps! Let me know if you're still having issues & I'll be happy to help out some more. 😊

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.