Help with formula for schedule health
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
Best Answer

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
Answers

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")))

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

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

Thanks so much Meg! All work now :)

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 ).
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!