Count cumulative working days for same task with or without date overlaps

Paula King

Dear Smartsheet Pros,

I would like to return the sum of the total number of working days for a specific Task Type when there may or may not be overlapping activities for different tasks of the same Task Type, but not count the overlapping work days. Ultimately, I would like to return the sum for each Task Type (there are 9 total) in a separate "roll-up" sheet for each Task Type or in a report (if this is possible). I have been only able to manually calculate this.

The attached provides an example from a grid/sheet for one of the Task Types "2 - Task B". The section identified for Task B.3 (4/11-4/22) has dates which overlap with Task B.2 which I would like to exclude from the total days worked sum formula. So, instead of the duration being 59 days, it would be 42 working days (14+18+10).

Thank you for your assistance!

Jake Gustafson

So your Duration has 59d, but you want to 1) exclude the two gaps between Tasks B.1 & B.2 and again between Tasks B.3 & B.4, and 2) avoid double counting the overlap within Tasks B.2 & B.3, right? Or are you including the gaps between tasks?

I'm not sure I have an answer, but I am curious to understand the problem better and see what others can suggest.

Paula King

Thanks for your interest, Jake.

Exactly. I only want to count the physical days of work for "2 - Task B" without the gaps and without double counting any overlaps (if they occur).

Pro Desk was not able to provide me with a solution and recommended I reach out to the community.

Jake Gustafson

@Paula King I've got a possible solution, I'll admit that it's not great and I'm not sure how scalable it is, but it can get you what you are looking for. Below is a screenshot and I had to tweak how your data was structured (which may not work for you).

Instead of the standard Duration column with dependencies, I used the NETWORKDAYS function to calculate the Duration for each Task (Rows 3-7) and a SUM(CHILDREN()) for Rows 1-2. As you can see I added Row 4 to allow the creation of the overlapping tasks to be children of that and calculate accordingly. In hindsight, I'd probably relabel the column 'Working Days' or something of the sort.

It might not be what you need, but maybe it'll get you or someone else thinking about another way to make this better.

Paula King

@Jake Gustafson I really appreciate your thoughts around this and I saw how this works in my test spreadsheet. This could work, but unfortunately, the overlaps occur for "2 - Task B" (and other Task Types) across 10+ projects. I would like to have a universal formula that I can apply to a template so the overlaps don't have to be identified to then apply the workaround. So for now, still looking...

AnonUser1

@Paula King I'm running into this issue now too...any solutions?

Paula King

Unfortunately not. I am working with Support to see if they are able to help with an automated solution.

Paula King

While waiting for Support assistance, I devised a work-around with a more of an automated solution using formulas to get closer to what I needed by removing tasks with timelines that fall within the timeline of other tasks in the same Queue/Task Type (e.g. Task A.2, Task C.2, Task C.4). This only works well if there is a complete overlap of tasks.

1. To return the maximum duration ("Max Duration") for each Queue/Task Type: =MAX(COLLECT({Duration}, {Queue}, Queue@row))
2. To return the start date for the Queue/Task Type maximum duration (Start2): =INDEX(COLLECT({Start}, {Queue}, Queue@row, {Duration}, [Max Duration]@row), 1)
3. To return the end date for the Queue/Task Type maximum duration (End2): =INDEX(COLLECT({End}, {Queue}, Queue@row, {Duration}, [Max Duration]@row), 1)
4. Sum if a task's start date is less than the Max Duration start date: =SUMIFS({Duration}, {Queue}, Queue@row, {Start}, <[Start2]@row)
5. Sum if a task's end date is greater than the Max Duration end date: =SUMIFS({Duration}, {Queue}, Queue@row, {End}, >[End2]@row)
6. Sum of 1, 4, and 5: =1 + 4 + 5

Unfortunately, staggered Queue/Task Types (e.g. Task B.2, Task B.3, Task C.5) still have the overlapping days counted with the Max Duration task for each Queue/Task Type which greatly skews the sum.

Refer to an example below with identified overlaps (S1-E2, S3-E1) that need to be removed, noted with an "X" (Start2=S1, End2=E1). I think I need two new formulas (or a combo formula of both) to result in an accurate count of non-overlapping working days for each Queue/Task Type. I tried the first formula A, but it failed with #INVALID OPERATION. I would also need the converse formula B to work. I could use some Smartsheet formula expertise with this.

Formula A =IF(HAS({Queue}, Queue@row), IF(AND({Start} < [Start2]@row, {End} > [Start2]@row, {End} < [End2]@row), NETWORKDAYS([Start2]@row, {Start})))

Formula B =IF(HAS({Queue}, Queue@row), IF(AND({Start} > [Start2]@row, {Start} < [End2]@row, {End} > [End2]@row), NETWORKDAYS({End},[End2]@row)))