Parents Misrepresenting Start & Finish Dates of Children

Chris Albig
Chris Albig ✭✭
edited 08/25/21 in Formulas and Functions

I have the Parent Rollup Functionality turned on as it is essential for my project to handle to dependencies. The problem I'm trying to overcome is that the Parent displays Target Start (TS) and Target Finish (TF) dates when not all children are populated in the same fields. If child#1 (of 2) has TS and TF dates populated, and child#2 does not, the Parent will display child#1's TS and TF dates. This is a problem because without child#2's dates considered, the Parent is displaying incomplete info.

I've reached out to support and was advised that this functionality does not exist, and as such I'm curious to see if anyone has a creative workaround. Further relevant info

  • In the scenario above child #2 is not populated for good reason and unavoidable,
  • A solution that requires manually filling in rows as they get added will not work as there are so many involved and it's an 800+ task project
  • In the screenshot example from my project the highlighted problem area is further complicating things by affecting tasks up to the great-grandparent level

I've got some great solutions in this forum when googling issues but this is my first time posting. I'm desperate!


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I see you have the predecessor set as row 234. Have you entered dates into that particular row yet?

  • Thanks for responding Paul, I've been helped by your previous inputs in this community! Predecessor 234 has no dates yet; this project is in a neverending discovery phase. I have my feelings about that but I'll keep them to myself!

    If the predecessors were all blank however, the problem persists. Throughout the project I have similar scenario's where blank dates corrupts Parents, Grand Parents, and so on.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    There is the problem then. You have to have some kind of date for it to be accounted for. If there is no date, then how will it know what to show on the parent row? You could end up putting in tomorrow's date for the predecessor which would impact the blank row in your screenshot by having it start the following business day (Monday) which would throw the end date out to 30 Aug which is 4 calendar days, or you could put in yesterday's date in which case the parent would only be impacted by 1 calendar day, or you could put in a date 1 year from now which would REALLY throw out that parent row's date.

    If you have no date, then how does it know what to output other than on the data that it does actually have?

  • I understand that dates are required. To answer your question, it shouldn't display what it doesn't know as is the case when not all child dates are present.

    My point is that logically, the parent dates should be blank until dates exist for all children. The Parent Rollup Functionality makes a bad assumption by displaying the minimum Start Date and the maximum Finish Date of it's children without considering whether all dates are present. How can it definitively say when the activity under a parent will start and finish without knowing all dates? For future functionality it would be ideal to allow user to toggle this as I can see the case for both sides depending on use case.

    I'm pursuing a creative way to overcome this so that people viewing the sheet and subsequent reports & dashboards are not presented with incorrect information.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    It is presenting based on the information it has without making assumptions on dates. If you want to "hide" parent rows that do not have all child dates filled in, I would suggest a helper checkbox column with the following formula...

    =IF(COUNT(CHILDREN([Task Name]@row)) <> 0, IF(OR(COUNTIFS(CHILDREN([Planed Start]@row), @cell <> "") <> COUNT(CHILDREN([Task Name]@row)), IF(COUNTIFS(CHILDREN([Planned End]@row), @cell <> "") <> COUNT(CHILDREN([Task Name]@row))), 1)

    This will check the box on parent rows where not all dates are filled in. From there you could use conditional formatting to change the text color to match the cell fill color on those rows where the boxes are checked. This would essentially "hide" those dates in the parent row.

  • Thanks Paul, I appreciate your creative approach. It's a partial solution yet it may deliver some value. I'll incorporate into my sheet.

    All the best

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!