Amount of Working Time Between Tasks

John Sauber

We have a desire to calculate the amount of work time between the end date of a task, and the start date of it's successor task.

 

Before the update that included partial day durations, we were performing this with simple date arithmetic. Now, that arithmetic does not hold true, and we need to devise an updated formula to carry out the calculation.

 

I have a test sheet going (here it is), which contains two simple projects, Alpha and Beta. There are individuals assigned to each task in each project. Frank, Bob and Edward happen to have tasks in each of these projects, and this causes some delays of what we call the "natural start date" for project Alpha. The definition we use for "Natural Start Date" is the date which a task could idealy start, given unlimited resources, according to the predecessors within that project. You can see that Beta.1 is causing Alpha.2 to be delayed by 1 work day. We consider the "Natural Start Date Delta" for Alpha.2 to be the begining of 9/29/2015, but another task is forcing it to be the begining on 9/30/2015. The "Natural Start Date Delta" for Alpha.2 in this event would be 1.

 

There is no gap between Alpha.2 and Alpha.3, so the Natural Start Date Delta is 0 here.

 

There is half-day gap between Alpha.3 and Alpha.4 because of Beta.2 (we don't want to overbook Bob, right?). The Natural Start Date Delta here is 0.5.

 

I have manually entered in the "Natural Start Date Delta" for each line where it is applicable, but I am looking for a formula to get there automatically. I have established (2) Text/Number and (2) Date column types for messing around freely (I can add more if anyone wants them). I have also have included the Dateonly() of the start and end tasks for each task in case someone wants to use those functions quickly.

 

Lastly, I am not looking to have this formula be automaticly follow a change in predecessors. Just think of the predecessor as known, and we can hard code any references to predecessor rows using the formula.

Brett Evans

John,

This is definitely possible.  I think the complexity depends on how many predecessor you have.  The complexity would be in the how you would iterate concatenations and parsings and the number of hidden columns used to store steps of the formula.  

 

So to solve this I geuss my question would be

1) how many predecessors  are allowed.  

2) What do you mean by "automaticly follow a change in predecessors" at the end of your post?  Could you expand upon that last paragraph?  I can definitely write a formula for a single cell that calculates this, but I am not clear on this functionality.

3) Are there more details or does your example sheet comer all posible cases?

 

Cheers,

Brett

 

 

 

 

John Sauber

Hi Brett, thanks for your reply. I agree that this certainly "feels" doable. To answer your questions:

 

1. 1) To keep it simple, let's assume that we are only talking about the predecessor within the project, in my example. Furthermore, we can assume that the predecessors do not change, and that there will be only one predecessor for each task in the project. If we can get this simplified scenario working, then I will take it to the next level.

 

2) What I meant here was that we can assume that predecessors for a given task will not change, and the number of predecessors in the given task will not change. I sort of clarified this in number 1) above.

 

3) The example sheet does not corner all cases (see the above assumptions). If we can get this simple case figured out, again, I'll extend the functionality to analyze multiple predecessors (which we are currently doing with full-day durations, only), using the =MAX function.

 

Does that sufficiently clarify things?

 

Feel free to work the sheet any way you wish. I can add more columns if you like, which generally help me devise more complicated formulas in stages, then combine all formulas at the end into one larger formula taking up just one column (see the "dateonly" columns in this example, for instance). 

John Sauber

We have been working with Smartsheet's support team on this topic, and I am happy to report that we (read: they) have come to an extremely elegant and simple solution!

 

There is a function, CALCDURATION(Date/Time, Date/Time). It's two required arguments appear to only work on the Date/Time column types (item 7 in these release notes) used in dependency-enabled sheets. Its normal back-end use is in a parent row of a hierarchy relationship, and it's two arguments are the start and end date/time columns in that parent row. The only way one would ever be able to see this is to show the duration column of a parent row in a report, select the duration cell of the parent row, and the formula will appear as hover-text. This same technique can be used to see how the Start and End Date/Time columns for a parent row are calulated, as well as another back-end formula used to roll-up the % Complete column in a parent row.

 

Back to the issue at hand. In our case here, we can use CALCDURATION() to calculate the difference between the start Date/Time of a task, and the end Date/Time of it's predecessor task(s).

 

The originally-referenced sheet has been updated and locked down to reflect this change.

 

Thanks to Travis, the Community Manager, and our Account Success Manager, Austen, for giving this topic their capable efforts to provide us with a fantastic solution a complex problem!

Travis

Happy to help! I am glad we were able to come up with a solution! 

 

Just as a caveat for anyone using the CALCDURATION function. This is not a function we make available publicly as we only use it on the backend on project sheets. Because this is not a public formula, our development team could change the functionality or remove it all together at anytime.  If you are okay with that, then feel free to use it!