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

SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…
Prevent due dates from falling within the same calendar week?
Hi. I am working on a (roughly) annual document review system, in partnership with another team in my organization. They have requested that they receive one document to review per week. But there are more documents to review than there are weeks in the year. So I need to figure out a method for ensuring that I don't send…
How to record date without time
I would like to use automation on a sheet to record the Open and Closed date for a given RAID item. I don't want to record the time, however. Is there a way to exclude a time stamp from the automation? If it is not possible to exclude the time stamp, what formula would I need to use to extract the datae and time into…