How to fix a formula for schedule health

katarinapetrovic

Hello! I have a functioning formula for schedule health for my project plan but it needs some changes and I just can't manage to do it without breaking it. The criteria for the current formula is

Blue – task is ‘’Complete’’

Green – task is ‘’In Progress’’ and on schedule

Yellow - task is ‘’In Progress’’ and End Date is 2 or less days away, OR up to 2 days past the End Date

Red - Status is ‘’In Progress’’ and more than 2 days have passed since the End Date

This is the current formula:

=IF(Status@row = "Complete", "Blue", IF(AND(Status@row = "In Progress", [End Date]@row > TODAY() + 2), "Green", IF(AND(Status@row = "In Progress", [End Date]@row <= TODAY() + 2, [End Date]@row >= TODAY() - 2), "Yellow", IF(AND(Status@row = "In Progress", [End Date]@row < TODAY() - 2), "Red", ""))))

And it works just fine. Now, I need to slightly change the criteria and incorporate it in the formula. These are the new criteria:

Blue – task is ‘’Complete’’

Green – task is ‘’In Progress’’ and on schedule

Yellow - task is ''In Progres'', starts 1 day after the End Date and continues up to 5 BUSINESS DAYS pass the End Date

Red - task is ‘’In Progress’’ and more than 5 BUSINESS DAYS have passed since the End Date

Could you please help me update my original formula? Thank you so much in advance!

Paul Newcome

Try this:

=IF(Status@row = "Complete", "Blue", IF(TODAY() > [End Date]@row + 5, "Red", IF(TODAY() > [End Date]@row, "Yellow", "Green")))

katarinapetrovic

Hi @Paul Newcome I tried but then it loses some features and doesn't work - I need the formula to follow the logic of the one we currently have, with the''In Progres'' functions and all, but just to modify the numbers in it for the colors to appear.

Paul Newcome

=IF(Status@row = "Complete", "Blue", IF(Status@row = "In Progress", IF(TODAY() > [End Date]@row + 5, "Red", IF(TODAY() > [End Date]@row, "Yellow", "Green"))))