Due Date Hierarchy

Ryan Campbell

Hello smartsheet community,

I have a sheet that consistently uses larger "buckets" or categories under a hierarchy and the due date column always reports the latest due date in the column. In case 1, the way due date hierarchy reports the due date and start date results in sooner due dates being overlooked and that might be an issue. For example. In the sheet that I use this in, I like to collapse things to minimize the amount of clutter I am looking at, but if I do that I do not get to know that Item 1 had a due date of 1-Jan-2020 by looking at the hierarchy titled Case 1- Tasks at Hand. Now in case 2, there is an ongoing project that has no due date, possibly like a routine task that happens twice a week or so. Given the two tasks above it are completed, then the Case 2 should be in the blue or green, because there is no finite due date coming up, however my task status column is showcasing that it is "overdue" or red, because it is getting the due date from one of the tasks that are marked as completed.

smartsheet community question example table.pdf

Basically, my main question is if there is a way that we can edit or add to the hard code of the start date/due date columns so it reports the first due date and not the last due date, and/or in my case 2 to ignore start/due dates for completed tasks.

We use this sheet in my team to track our workload so deleting the rows after completed is not a practical solution.

Thanks!

Ryan

Paul Newcome

Are you using dependencies? If you are using dependencies, then you cannot. If you are not using dependencies, formulas can be written. I would be happy to help with formulas if you are able to use them.

Paul Newcome

Are you using dependencies? If you are using dependencies, then you cannot. If you are not using dependencies, formulas can be written. I would be happy to help with formulas if you are able to use them.

Ryan Campbell

Hi Paul,

Yes I am using dependencies. Thank you for letting me know we cannot do this in smartsheet. I do wish it would recognize when something was completed not to consider it as a due date, but it seems like with dependencies enabled I cannot. Thanks! I appreciate it.

Paul Newcome

Happy to help! 👍️

If you are not using predecessors, the dependency functions can actually be pretty straight forward to replicate through formulas which would allow for the adjustment in the rollup rows that you are looking for.

Ryan Campbell

I did see some other posts in Community where something similar was created to edit the dependencies. The difficult part is that this is envelopes about 10 smartsheets into one report and I am one of the ones in my team that is most familiar with smartsheet formulas and cannot manage to edit all of theirs all of the time. Especially because our tasks fluctuate and projects open and close fluidly. We are not using predecessors because a lot of things can be done simultaneously under our hierarchy and not necessarily dependent on the next row or a subsequent row.

Thanks for your help!

Paul Newcome

Is it feasible to create a template project sheet that already has formulas built into it that the rest of your team can use?

Basically a generic project sheet where they can tweak task names and only certain dates and the rest of the dates are populated through a formula?

Ryan Campbell

If it becomes an issue I will consider it, but right now it is only a minor inconvenience. I do see what you are saying though, because I have built templates in the past. Thank you for your suggestion!

We will move forward with how it is, and consider remaking all of them into a template like you have described if it causes any issues with other members in my team. Thanks!

Paul Newcome

Happy to help! 👍️