Adding column of summary date values

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.

Find more posts tagged with

Formulas

Conditional Formatting

Gantt View

Comments

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.

Smartsheet explanation.PNG

Sign in to join the conversation!

Quick Links

Trending discussions

Calculate Project Time TOTAL for PM across aligned projects
Hi - I am trying to figure out how to create a SUM of the Total Project Time aligned to each PM for all of their projects. On my master sheet I have the Resource Type, Resource Name, and a Helper Column for Time. The Helper Time is an estimated allotted time based on the timeframe of the project. Example: <3 months = 25…
How to Create Time Duration
I have a report where I need a formula to figure out time duration. For example, employee starts at 8:00 am and ends at 9:00 am, I need the next column to tell me 1.00 to indicate he worked 1 hour. I will be using military time throughout this sheet.
Formula with range data
Hello, I'm looking to see if anyone knows a formula that would add owner income and co-owner income together to reflect in Joint Income column. I'm not sure how to do this with data in ranges. Thank you.