Multiple IF/AND Formulas - Need Nesting Help

Tiffanie Teater

I am in need of some assistance with a formula.

Here is a snap shot of the sheet I am working with:

I am trying to create a formula for the Activity Health Status field that drives the RYG ball. 

I want it to do the following:

Blue

Tasks that show status of “Complete”

Red

Tasks that show status of “In Progress” and finish date is within the next 2 days and % Complete is less than 50%

Tasks that show status of “In Progress” and finish date is in the past

Tasks that show status of “Not Started” and the start date is in the past

Tasks that show status of “Not Started” and the finish date is in the past

Yellow

Tasks that show status of “In Progress” and finish date is within the next 4 days and % Complete is less than 50%

Green

Tasks that show a status, start and finish date and % complete but do not meet the criteria above

Error

If there is an error (start/finish dates, % complete, or status fields are blank etc) – No color appears

I have tried myself to come up with an IF formula; however, my nesting skills are not that great so it always comes up as UNPARISHABLE. I have reviewed info on the IF, IFERROR, NETWORKDAYS, AND/OR etc but cannot figure it out.

This is something needed ASAP. Can you assist?

I have gotten this far with the formulas (I think these are correct individually); however, I do not know how to nest them together:

Blue

1. Tasks that show status of “Complete”

=IF([% Complete]@row=1, "Blue")

Red

2. Tasks that show status of “In Progress” and finish date is in the past

=IF(AND(Status@row = "In Progress", TODAY() > [Finish Date]@row), "Red", "")

3. Tasks that show status of “Not Started” and the start date is in the past

=IF(AND(Status@row = "Not Started", TODAY() > [Start Date]@row), "Red", "")

4. Tasks that show status of “Not Started” and the finish date is in the past

=IF(AND(Status@row = "Not Started", TODAY() > [Finish Date]@row), "Red", "")

5. Tasks that show status of “In Progress” and finish date is within the next 2 days and % Complete is less than 50%

=IF(AND([Status]@row ="In Progress", (NETWORKDAYS(TODAY(), [Finish Date]@row) <3), [% Complete]@row <50), "Red", "")

Yellow

6. Tasks that show status of “In Progress” and finish date is within the next 4 days and % Complete is less than 50%

=IF(AND([Status]@row ="In Progress", (NETWORKDAYS(TODAY(), [Finish Date]@row) <5), [% Complete]@row <50), "Yellow", "")

Green

Tasks that show a status, start and finish date and % complete but do not meet the criteria above

Put “Green” at the end of the formula?

Error/If Blanks

If there is an error (start/finish dates, % complete, or status fields are blank etc) – No color appears

Put IFERROR and “” at the end of the formula? 

Any assistance would be greatly appreciated!

Paul Newcome

Try this one...

=IF(OR([Start Date]@row = "", [Finish Date]@row = "", [% Complete]@row = "", Status@row = ""), "", IF(Status@row = "Complete", "Blue", IF(OR([Finish Date]@row < TODAY(), AND([Start Date]@row < TODAY(), Status@row = "Not Started"), AND(NETWORKDAYS(TODAY(), [Finish Date]@row) <= 2, [% Complete]@row < .5)), "Red", IF(AND(NETWORKDAYS(TODAY(), [Finish Date]@row) <= 4, Status@row = "In Progress", [% Complete@row < .5), "Yellow", "Green"))))

Tiffanie Teater

I tried this but it still reads as UNPARISHABLE.

Someone else used the info I had above and came up with this that seems to work:

=IF(ISBLANK(Status@row), " ", IF(ISBLANK([Start Date]@row), " ", IF(ISBLANK([Finish Date]@row), " ", IF(ISBLANK([% Complete]@row), " ", IF([% Complete]@row = 1, "Blue", IF(AND(Status@row = "In Progress", TODAY() > [Finish Date]@row), "Red", IF(AND(Status@row = "Not Started", TODAY() > [Start Date]@row), "Red", IF(AND(Status@row = "Not Started", TODAY() > [Finish Date]@row), "Red", IF(AND(Status@row = "In Progress", (NETWORKDAYS(TODAY(), [Finish Date]@row) < 3), [% Complete]@row < 50), "Red", IF(AND(Status@row = "In Progress", (NETWORKDAYS(TODAY(), [Finish Date]@row) < 5), [% Complete]@row < 50), "Yellow", "Green"))))))))))

It seems to work; however, it appears yours is much more condensed. Is there a way to condense the one I have listed above?

Tiffanie Teater

I think I got yours to work! It was just missing a bracket ] at the end of the formula. You had:

[% Complete@row < .5), "Yellow", "Green"))))

I added the bracket:

[% Complete]@row < .5), "Yellow", "Green"))))

And now it works!

=IF(OR([Start Date]@row = "", [Finish Date]@row = "", [% Complete]@row = "", Status@row = ""), "", IF(Status@row = "Complete", "Blue", IF(OR([Finish Date]@row < TODAY(), AND([Start Date]@row < TODAY(), Status@row = "Not Started"), AND(NETWORKDAYS(TODAY(), [Finish Date]@row) <= 2, [% Complete]@row < .5)), "Red", IF(AND(NETWORKDAYS(TODAY(), [Finish Date]@row) <= 4, Status@row = "In Progress", [% Complete]@row < .5), "Yellow", "Green"))))

Couldn't thank you enough!

Paul Newcome

@Tiffanie Teater Sorry about that. Those brackets always get me. Ugh. I'm glad you were able to catch that!