Adding column of summary date values

Lindsay AR

I have a "Date Completed" column in my list that I would like to roll up like Start and Due Date do with sub-tasks in the hierarchy. I have been unable to figure it out with formulas. So, I am looking for the last date entered in "Date Completed" of a sub-task to auto fill that same date into the highest level of the task. 

Andrée Starå

Hi Lindsay,

Will the row change anything after the Date Completed cell is selected?

I hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Andrée Starå

And.

Am I understanding you correctly that you want to show the last date added and not the latest date?

Paul Newcome

I am leaning towards LATEST date being the requested info based off of the comparison to the Start and End Date rollups.

 

If that is the case, you can just use something along the lines of this...

 

=MAX(CHILDREN())

Lindsay AR

This formula worked! Is there a way to dictate, if it has children, then this formula, otherwise no formula?

I have a few lines that I want to work like the hard coded due date column. Pretty much, I am looking for a way to make my own summary value based on the hierarchy. 

Andrée Starå

Yes, there is.

Try something like.

=IF(COUNT(CHILDREN([Task Name]@row)) > 0; MAX(CHILDREN()))

The same version but with the below changes for your and others convenience.

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, MAX(CHILDREN()))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Paul Newcome

Andree's solution is correct for determining whether or not it has children. Just keep in mind...

 

While this will leave those cells blank that do not have any children, once a date is manually entered that formula will be overwritten and removed from the cell. If you were to adjust the hierarchy after manually entering a date, then you would need to replace the formula in the cell.

Lindsay AR

Yeah, with many users in a sheet, I can't see this being a good solution. I solution where a user could add their own summary column similar to that of duration and end date with hierarchies would be very useful.  

Paul Newcome

Lindsay,

 

This sounds interesting. Can you explain further? Are you able to build a mock up of sorts to provide screenshots of what it would look like and exactly how it would work?

 

That way either we can help you find a more fitting solution than those already presented, or you will have a good foundation with which to Submit a Product enhancement Request.

Lindsay AR

In this example, I want the columns with the thick red arrows to roll up the same way the native columns in project sheet do based on hierarchy (Due Date column was renamed from End). 

I am currently doing this in the Hours column with formulas; the formula gets erased though as the sheet gets manipulated which makes it not an effective way to move forward. 

It would be advantageous to be able to add hard coded columns like the ones provided. This way the sheet can still be manipulated without fear of losing formulas, data, and keeping data integrity not only within the sheet, but also reports/dashboards.