Project Management Plan - Schedule Health Formula
Hello,
I looking to apply a formula to my Project Management plan whereby a color is assigned to each row given the "% Complete" and the "Start Date" "Finish Date". I want the health of the children rows to roll up into the Health of the parent row.
I've used one of the templates with a built-in formula, however it does not work / accurately reflect status (ie status is still red when a task is complete).
Reference Cells: Start Date, End Date, % Complete
Blue = Task Start Date is greater than Today and % Complete is equal to 0
Green = Task Start Date is greater than Today and % Complete is greater than 0
Green = Task End Date is greater than Today and % Complete is greater than 0
Green = % Complete is equal to 1
Yellow = Task Start Date is less than Today and % Complete is less than 1 but greater than 0
Red = Task Start Date is less than Today and % Complete is equal to 0
Red = Task End Date is less than Today and % Complete is less than 1
As mentioned, if someone could assist with this, I would be very appreciative!
Answers
-
@ClaireWallace That's a bit to hash out here in this post, but I have a couple of videos that together might give you some direction.
This one covers using the CHILDREN() and DESCENDANTS() functions to roll up values into the parent rows:
This one covers some about creating the formula for the health status column:
Darren Mullen, join the Smartsheet Guru Elite
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
Hi Darren,
Thanks for your response. I'm building out the health status formulas right now. I figured I'd build each component of the formula individually and combine them. The first part of my formula works (the yellow portion), but when I added in the next conditions, the very same structure did not work.
=IF(AND([End Date]@row > TODAY()), Status@row = "In Progress", "Yellow"), IF(AND([End Date]@row < TODAY()), Status@row = "Not Started", "Red")
Any ideas?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!