Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Amount of Working Time Between Tasks
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.