Need Help with IF, AND to create a Health Matrix that is based on DATE & % Complete

I have figured out most of the equation to display the health of a task but I can't figure out how to get the Last criteria figured out.

Sample Case:

Task is 10 days in duration 1. Health is "Green" up to the 5th day. 2. If the task is below 50% complete and past 5 days then "Yellow". 3. If the task is above 50% complete and past 5 days then "Green". 4. If the task is past the 10 days and is not 100% complete then "Red". If the task reaches 100% then green. The task always starts out "Green" until the 5 day past the "Start Date".

This is what I have so far that is working but I don't know how to report on past the 10th day and NOT at 100% Complete. I can figure it out by itself but the rest of the equation is creating issues.

=IF(AND([Start Date]4 + 5 < TODAY(), [%]@row < 0.5), "Green", (IF(AND([Start Date]4 + 5 < TODAY(), [%]@row > 0.5), 2, (IF(AND([Start Date]4 + 5 > TODAY(), [%]@row < 0.5), "Yellow", (IF(AND([Start Date]4 + 5 > TODAY(), [%]@row > 0.5), 3, (IF(AND([End Date]< TODAY(), [%]@row <1),”Red”,9)))))))

Output: GREEN, 2 this is just to check the logic, Yellow, 3 this is just to check the logic, Red, 9 this is to see if any of the logic was used.

Thanks

Find more posts tagged with

Accepted answers

All comments

Genevieve P.

Hi @thunt06

The first thing to note is that with a Nested IF statement, you can jump right into the next IF without adding an open parentheses in front of it. Not this, (IF, but this, IF.

I would also suggest replacing your row number reference with @row, and combining your first two statements for Green to simply be that if 5 days past the Start Date is still in the future, it’s Green (no matter what the percentage is). I added in an OR to say that if it’s 100% (no matter what the dates are), then that’s also green.

Finally, your greater than and less than signs (> and <) were actually backwards. Greater than, >, indicates that the date is in the future. Less than, <, is looking for if the date is in the past.

I have re-organized your statements, since Logic formulas read left-to-right and stop as soon as one criteria is met.

Try this:

=IF(AND([End Date]@row < TODAY(), [%]@row < 1), "Red", IF(OR([Start Date]@row + 5 > TODAY(), [%]@row = 1), "Green", IF(AND([Start Date]@row + 5 <= TODAY(), [%]@row <= 0.5), "Yellow", IF(AND([Start Date]@row + 5 <= TODAY(), [%]@row >= 0.5), "Green"))))

Here are some Help Center articles that I used:

IF function / OR function / Formula Operators, such as Less Than / @row function

Let me know if this works, or if you have any questions!

Cheers,

Genevieve

Paul Newcome

We can actually shorten it even further with just a little bit of rearranging. If you put the "Green" section first, you can remove the AND from the "Red" portion since you are already establishing that [%]@row is less than 1 by default if it is able to move past the first portion, and using a nested IF logic on the "Yellow"/"Green" set with [Start Date]@row + 5 <= TODAY() means we can shorten that part a little bit as well by only having to type that once instead of twice within two different AND statements.

=IF(AND([End Date]@row < TODAY(), [%]@row < 1), "Red", IF(OR([Start Date]@row + 5 > TODAY(), [%]@row = 1), "Green",

can be shortened to

=IF(OR([Start Date]@row + 5 > TODAY(), [%]@row = 1), "Green", IF([End Date]@row < TODAY(), "Red",

IF(AND([Start Date]@row + 5 <= TODAY(), [%]@row <= 0.5), "Yellow", IF(AND([Start Date]@row + 5 <= TODAY(), [%]@row >= 0.5), "Green"

can be shortened to

IF([Start Date]@row + 5 <= TODAY(), IF([%]@row <= .5, "Yellow", "Green"

Which takes your entire formula from

=IF(OR([Start Date]@row + 5 > TODAY(), [%]@row = 1), "Green", IF([End Date]@row < TODAY(), "Red", IF([Start Date]@row + 5 <= TODAY(), IF([%]@row <= .5, "Yellow", "Green"))))

@Genevieve P That second portion is a good example of where we were talking about using a nested IF in place of replicating the same criteria within multiple AND statements.

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.