Need help with nested IF equation

I have an IF equation that is working fine for me, however, I would like for anything that is past due to be "Red" in the task health bar. Right now, because I have "Grey" as the first clause even if an item is beyond it's end date, if it is "0%" complete it shows as "Grey." How do I adjust the formula to make any thing that is past it's due date "Red?"

I've tried moving things around and I'm sure it's a simple answer, but at this point I'm too brain dead to figure it out.

=IF([% Complete]@row = 0, "Gray", IF(AND(TODAY() > WORKDAY([End Date]@row, -1), [% Complete]@row < 0.9), "Red", IF(AND([% Complete]@row < 0.5, NETWORKDAYS([Start Date]@row, TODAY()) / Duration@row > 0.5), "Yellow", "Green")))

Find more posts tagged with

Sheets

Formulas

functionality

Accepted answers

KPH

Hi @TB0944

The first IF turns everything that is 0% complete to gray before considering anything else. If you want anything past due to be red regardless of how complete it is, that IF needs to be ahead of the one for gray.

Current formula (I've used a screen shot so I can indent to make this more readable - so you can see the changes)

Swapping the red and gray (as below) around would mean anything past due and that is less than 90% complete is red. But something that is 95% complete and past due would still be green.

Changing the <0.9 to <1 would mean all past due are red.

Then any not in the past and not 100% but are 0% completed, are gray.

Then, if the tasks are neither in the past and 100% completed or not 0% completed, the yellow/green statement is evaluated.

Here is the formula to copy

=IF(AND(TODAY() > WORKDAY([End Date]@row, -1), [% Complete]@row < 1), "Red",

IF([% Complete]@row = 0, "Gray",

IF(AND([% Complete]@row < 0.5, NETWORKDAYS([Start Date]@row, TODAY()) / Duration@row > 0.5), "Yellow",

"Green")))

All comments

KPH

Hi @TB0944

Current formula (I've used a screen shot so I can indent to make this more readable - so you can see the changes)

Swapping the red and gray (as below) around would mean anything past due and that is less than 90% complete is red. But something that is 95% complete and past due would still be green.

Changing the <0.9 to <1 would mean all past due are red.

Then any not in the past and not 100% but are 0% completed, are gray.

Then, if the tasks are neither in the past and 100% completed or not 0% completed, the yellow/green statement is evaluated.

Here is the formula to copy

=IF(AND(TODAY() > WORKDAY([End Date]@row, -1), [% Complete]@row < 1), "Red",

IF([% Complete]@row = 0, "Gray",

IF(AND([% Complete]@row < 0.5, NETWORKDAYS([Start Date]@row, TODAY()) / Duration@row > 0.5), "Yellow",

"Green")))

TB0944

@KPH That worked perfectly! Thank you so much for the thorough explanation.

KPH

Happy to help, @TB0944

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.