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