Have you ever been asked to calculate something based on business or working hours? My guess is that you have.

These things are pretty common, especially when working with clients that want to assess their productivity, sales, costs or pretty much anything else that involves working hours.

So, in this blog, I will show you the workflow that me and @Soha Elghany made for a client project at the Data School.

I will be talking about Step 1 and Soha will talk about Step 2 and 3. For further information on Step 2 and 3, please see her blog post here.

This is the final workflow that we made.

So, in our initial dataset, we were given the created date, which would be your start date, and a resolution date, which would be your end date.

The main goal was to calculate the time it took to get from start to end date, only considering business hours, i.e. how long it took to resolve the issue, in the form of a ticket.

Our first step was to create a new start date for all the tickets that came in outside business hours. This included weekends and the nights during the weekdays.

In our case, business hours were from 8am to 6pm, so if any tickets fell outside of these times, we wanted to create a new start date to the next working day at the earliest time.

For example, if a ticket came in on Saturday afternoon, we wanted to move this to the next Monday at 8am. Similarly, if the ticket came in on a Monday after 6pm, we wanted to move this to Tuesday 8am.

Excluding Weekends

To exclude the weekends, we needed to change our original date/time format to days. We used the following formula’s. We also created a new field called “create date exc weekends”, which changed the date to Monday.

We then used a filter to identify tickets that came in at the weekend. The data coming from the true node of the filter would need to be dealt with, with the following steps. For the data coming from the false filter, please see the next section titled “Weekdays”.

However, the formula added 48 and 24 hours to the old date, whereas we wanted the new start date to be at 8am on Monday, regardless of the time it came in at the weekend.

First, using a select tool, we had to change the new date field from “string” to “datetime”. We then had to change the newly created date to days, using the following formula.

Once we changed the dates to a day format, we had to change it back to a string, and add the following formula.

Weekdays

The next part is about dealing with the weekdays. This is what this part of the workflow looked like:

From the filter, you would need to connect the false node to a formula tool, with the following configuration. We also created a new column called “business hours”.

We then wanted to filter out the tickets that were created after 6pm. Once this was filtered, we then needed to change the hours to 8am the following working day.

To do this, we repeated the same steps as we did with the weekend days. But instead of replacing the column “create date exc weekends”, we replaced “business hours” to change the new create date to 8am.

Union

We then used a Union tool to collate all the data together.

At this point, you should have all the correct start dates. For the next steps, please see Soha’s blog here.