Calculating days between tasks while taking in account for business days

[rdeguzman]

Hi Community,

I am trying to build a workflow that will calculate some SLAs between tasks, but I have to take in account for Business days only.

Note: I have looked at a lot of the workflows but they don't seem to be what I am looking for. Maybe there is one that I missed?

Attached is my current workflow with dummy data added in and the calculations I currently have for calendar day, but just cant seem to get the business days to work.

[AngelosPachis · Accepted solution]

Hi @rdeguzman ,

I would try a slightly different approach to answer that question. The first thing I would do is pivot the data to bring the table in a long format. That may not seem intuitive in the first place, but it will make it easier to process the data.

After parsing the date string to a date data type, you can use a multi-row formula to find the start and end dates for each task for each service ID. For task #1, the start date would be unknown, but either way you were trying to estimate the dates between task 1 and task 2 and so on.

Then with a generate rows tool, you can create all dates between the start date and end date of each task, find the weekday with a formula tool and then exclude the weekends (keep only business days).

Finally with a summarize tool, you can find the dates passed between each task.

You can then process the data further if you want to find the time to completion between task 1 and task 5 by aggregating the [Bus Days to task completion] column accordingly.

Hope that helps,

Angelos

[rdeguzman]

@AngelosPachis That helped get me on the right track. THe problem I have is with my actual data, I have more tasks (47 to be exact) and between task 1 and 2 there are 3 others...and so on. So I was able to filter out the specific tasks and use your work to help with mine.

Thanks!