Parent Date Roll ups - Possible for two sets of dates?

Scott Houle

I have a head scratcher here, thanks in advance for any help that anyone may have. 

 

My scenario is this, I have project plans that have two sets of dates, one is estimated dates and the other is the actual dates on the project tasks. In my project settings the estimated dates are linked to the dependencies. 

 

When I enter in my start and end dates on my estimated columns, all of the dates populate and the roll up appears at the top of the sections, no problems. However, the actual dates do not roll up (expected). 

 

I have found three different options to get the dates to appear in the parent row for the actual dates. All three have trade off's and i'm wondering if there is a better way to do this. 

 

1) After populating my estimated dates I can change the project settings to now use the Actual Dates. This causes the Actual Dates to populate based on the dependencies. This is not ideal as project team members populate these dates so we can track the lead time on tasks and are supposed to fill this information out. 

 

2) Similar to above I change the project settings to use the actual dates, however this time I create a dummy predecessor and duration column that I leave blank and make them my predecessor and duration columns in the Project Settings. This mostly works, except if we ever need to change a predecessor, duration or estimated date (I know, these "shouldn't" be changing) it either doesn't update or we have to go in and change the Project Settings, then change them back. 

 

This mostly works, except i'm not a fan or the overhead or the chance of a user changing a date and it not flowing through to the rest of the sheet. 

 

3) My last option is to simply add a formula to the parent rows in the correct cells to identify the first and last actual start and end dates. This also works, my fear is users accidentally deleting the formula's, tasks being out of sequence, and frankly the manual work of updating sheets with formulas in all areas that require a roll up to a parent row. 

 

 

My gut is telling me that option #2 is my best bet, but I was wondering if anyone else had run into this and had a better solution, or if there was an established best practice that i'm not aware of. 

Thanks in advance. 

J. Craig Williams

Scott,

 

I've had problems switching back and forth between two sets of dates, but perhaps not quite in the way you describe.

That said, I would likely go with #3.

 

It won't be too time consuming to populate the formulas

1. Create a column that determine parent or child

2. Filter on parents

3. Update formulas with copy/paste

4. Unfilter

 

Delete column if you don't need it / want it.

 

To avoid deletion, lock the rows (when they are filtered)

 

I don't know what you mean by out of sequence.

The task dates should be the same (at least at the start of the project) so the roll-ups should also match.

 

I also might keep columns to track variance between planned vs actual.

 

Hope this helps.

 

Craig

 

 

Scott Houle

Thanks for the response Craig. 

 

I ended up copying one of the sheets in question and applied both options to it, and you were correct, option #3 was by far the better way to go. I used your tip on populating the formulas and it made the job much more manageable. 

J. Craig Williams

Glad I was helpful.

 

Craig

jay.krish77951

Hi Scott

I have the same issue and you have used a formula to the parent rows in the correct cells to identify the first and last actual start and end dates.

Will it be possible for you to share the formula, as I am not well versed with the code.

Thanks

Jay

jay.krish77951

Hi Craig.

I would appreciate if you also can help with the formula mentioned above.

Thanks

Jay

jay.krish77951

Hi Scott

I have the same issue and you have used a formula to the parent rows in the correct cells to identify the first and last actual start and end dates.

Will it be possible for you to share the formula, as I am not well versed with the code.

Thanks

Jay

jay.krish77951

Hi Craig.

I would appreciate if you also can help with the formula mentioned above.

Thanks

Jay

Josh Garcia

For the Actual Start Date, you'll want to select the earliest date of the set, so use:

=MIN(CHILDREN())

For Actual Finish Date, you want the latest date, so:

=MAX(CHILDREN())

For the hidden field to filter only on rows that have children (to facilitate maintenance of the formulas as the sheet is used), you'll want a simple checkbox using this formula:

=IF(COUNT(CHILDREN([FIELD_NAME1])>0,1,0)

Note that you'll want to replace FIELD_NAME1 with the name/row number of a field in your sheet. Select one that will always have a value - the count(children()) function does not count rows, it counts the number of populated values in the children of a given cell's children.

For my purposes, I selected the primary field name, since I'll always populate that field.

 

Josh