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!

Find more posts tagged with

Formulas

Accepted answers

All comments

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?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.