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!
