Formula isn't working properly

dmsbhs

I created the below formula to calculate project schedule health and cannot seem to have it return expected outcomes. Can someone help me get this right?

Red

• If a task is not completed and the target date is either today or in the past; OR
• if the end date (end date is captured when %Compete is 100%) is 5 days after the target end date (I'd like this to be 5 net work days if possible)

Yellow (this one is a bit more involved)

• if % Complete is less than than (count of days since start date)/(duration) difference of start date and today (the formula has a *-1 and a -1 to not count the first day and keep responses positive); OR
• if end date is within 5 days of target end date

Green

• default to green if blank
• if the above conditions aren't found should be green
• if completed on or before target end date

=IF(OR(AND(Status@row <> "Complete", [Target End Date]@row <= TODAY()), [End Date]@row > [Target End Date]@row + 5), "Red", IF(OR([% Complete]@row < ((((NETWORKDAYS(TODAY(), [Start Date]@row)) * -1) - 1) / Duration@row), [End Date]@row > [Target End Date]@row + 5), "Yellow", "Green"))

Edited to bold logic that isn't working properly and added third bullet to Green

Hollie Green

If you want it to be yellow if it is within 5 days after the target date you need to change it to < than instead of >

[End Date]@row < [Target End Date]@row + 5),"Yellow"

Will also need to add criteria for [End Date]@row>[Target End Date]@row so it won't mark it as yellow until the Target End Date has passed.

If you are wanting it to be yellow within 5 days before the Target end Date then you would just need to change the + 5 to a -5