Set Parent's Projected Date based on Children's Status and Projected Date

Heather Walker

I am using several layers of parents + children rows, and I need the duration / projected date to be "TBD" when a child row is "In Progress" or " Not Started."

Here's the current situation -

when a child row has a date set, it rolls up to the parent, which is inaccurate as a projected end date since there are still items Not Started:

What I'm thinking:

IF all Children are Status = Complete, Parent Projected Date = "Complete"

IF any Children are Status = "Not Started", Parent Projected Date = "TBD"

IF any Children are Status = "In Progress", Parent Projected Date = "TBD"

Issues I'm encountering:

The Projected Date column is a Date column in Project settings for the Gantt view. I am not sure how to enter text into a Date column.

How do I write out this logic?

Do I change the Projected Date column to a Text column, and if so, how do I do the Gantt dependencies still?

Any help would be greatly appreciated!

Thanks,

Heather Walker

Genevieve P.

Hi @Heather Walker

It sounds like you are using Dependencies in your sheet. If so, you won't be able to put a formula into any Date column used in your Project Settings or Duration column (see: Project Sheet Columns: Start Date, End Date, Duration, % Complete and Predecessors). You also wouldn't be able to change this column to be a text column because it would break your Project settings.

Instead, you could have a helper column that identifies this status, or if the row is a child row. Then you could use Conditional Formatting to make the values of your Duration and Projected Date look blank or greyed out until they are complete.... perhaps like so:

The formula I used in my helper "Percent Complete" column on the left is:

=IF(COUNT(CHILDREN([Task Name]@row)) = 0, "Child", IF(COUNTIF(CHILDREN(Status@row), OR(@cell = "In Progress", @cell = "Not Started", @cell = "")) > 0, "TBD", "Complete"))

If there are no Children for this row, then it's a "Child" row.

Otherwise, if the COUNT of Children that say "In Progress" or "Not Started" (or are blank) is greater than 0 (so there's at least one child that has either of these options), then it's "TBD". Otherwise, it's "Complete."

Would this work for you?

Cheers!

Genevieve

Genevieve P.

Hi @Heather Walker

It sounds like you are using Dependencies in your sheet. If so, you won't be able to put a formula into any Date column used in your Project Settings or Duration column (see: Project Sheet Columns: Start Date, End Date, Duration, % Complete and Predecessors). You also wouldn't be able to change this column to be a text column because it would break your Project settings.

Instead, you could have a helper column that identifies this status, or if the row is a child row. Then you could use Conditional Formatting to make the values of your Duration and Projected Date look blank or greyed out until they are complete.... perhaps like so:

The formula I used in my helper "Percent Complete" column on the left is:

=IF(COUNT(CHILDREN([Task Name]@row)) = 0, "Child", IF(COUNTIF(CHILDREN(Status@row), OR(@cell = "In Progress", @cell = "Not Started", @cell = "")) > 0, "TBD", "Complete"))

If there are no Children for this row, then it's a "Child" row.

Otherwise, if the COUNT of Children that say "In Progress" or "Not Started" (or are blank) is greater than 0 (so there's at least one child that has either of these options), then it's "TBD". Otherwise, it's "Complete."

Would this work for you?

Cheers!

Genevieve

Heather Walker

Hi @Genevieve P -

This is a great solution while keeping my dependencies, thanks so much!

-Heather

Genevieve P.

No problem! Let me know if you need any help with the formula. 🙂