Project Management Plan - Schedule Health Formula

ClaireWallace

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!

Darren Mullen

@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:

https://youtu.be/Dzo0UYjxMmI

This one covers some about creating the formula for the health status column:

https://www.youtube.com/watch?v=bqyNixwqrO4

ClaireWallace

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?