Help with formula for schedule health

Emily S

Hello, looking for some help with calculating schedule health.

I have added a symbols column (Green, yellow, red, gray circles) called schedule health to schedule.

I have started creating a formula to calculate the schedule health for each row. This is what I have based the formula on:

• On Track = Green. Occurs when ‘% complete’ is ‘100%’ before reaching or on the due date
• At risk = Yellow. Occurs when ‘% complete’ is not equal to 100% 5 days after the due date
• Action required = Red. Occurs when ‘% complete’ is not equal to 100% 10 days after the due date
• On hold = Grey. Occurs when the status is set to ‘On hold’ 

This is the formula I have used:

=IF([% Complete]@row = 1, "Green", IF(TODAY() > [End Date]@row, IF([% Complete]@row < 1, "Yellow", IF(TODAY(5) > [End Date]@row, IF([% Complete]@row < 1, "Red", IF(TODAY(10) > IF(Status@row = "On Hold", "Gray")))))))

The formula is working but I have noticed it is not set up right.

For example, when the status is on hold but the % complete is 50% it is showing up as yellow but I would like it to be gray to show it is now on hold. Also, the red 'action required' part of the formula doesn't seem to be working. The symbol is showing up as yellow for everything not 100% past the due date.

Part 2

I would like to calculate the overall schedule health of all the rows.

For example, if 90% of the schedule rows are Green then the overall health should be green.

If anyone has a formula I could use the calculate the overall health of the schedule that would be really helpful.

Thanks,

Emily

Schedule example:

Formula example

Meg Y

Made a modification to the formula Michael shared. Try it and see if works.

=if(Status@row = "On Hold", "Gray",if(and([% Complete]@row = 1,TODAY() > [End Date]@row),"Green",if(and([% Complete]@row < 1,TODAY(5) > [End Date]@row),"Yellow",if(and([% Complete]@row < 1,TODAY(10) > [End Date]@row) if"Red"))))

Meg

Michael Culley

The way you did the formula isn't correct.

Try this:

=if(Status@row = "On Hold", "Gray",if(and([% Complete]@row = 1,TODAY() > [End Date]@row),"Green",if(and([% Complete]@row < 1,TODAY(5) > [End Date]@row),"Yellow","Red")))

Emily S

Thanks, Michael. That fixed the gray status problem.

At the minute the formula is showing a status of yellow for anything not complete past the due date. I would like the status to be yellow for anything not completed 5 days after the due date and red for anything that is more than 5 days past the due date.

If you could provide me with that formula that would be very helpful.

Thanks,

Emily

Meg Y

Made a modification to the formula Michael shared. Try it and see if works.

=if(Status@row = "On Hold", "Gray",if(and([% Complete]@row = 1,TODAY() > [End Date]@row),"Green",if(and([% Complete]@row < 1,TODAY(5) > [End Date]@row),"Yellow",if(and([% Complete]@row < 1,TODAY(10) > [End Date]@row) if"Red"))))

Meg

Emily S

Thanks so much Meg! All work now :)

Meg Y

@Emily S

Happy to hear it worked. Below is what I do when a formula is long/complicated and/or is giving me a error. It helps me make sure all the puncation marks needed are there. 😀

Break it down by each function and test it on a row. Once they all work, combine them in order of needed.

What I did with the formula Michael created was copy the formula for "Yellow", pasted it on the end and changed the color to "red". Just make sure when you do that to add the extra ending ).