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
Meg Young
mmyoungconsulting@gmail.com
If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks!
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
Meg Young
mmyoungconsulting@gmail.com
If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks! -
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 ).
Meg Young
mmyoungconsulting@gmail.com
If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 213 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!