The most common request you will receive is to calculate something based on work hours especially concerning questions around productivity, hours and sales.

Laine has a detailed blog post on Part 1 of calculating business hours which you can access: here

I will be detailing part 2 and 3

This is what our final workflow looked like:

At this point, you would have cleaned and sorted you start date to correspond to business working hours.

The main goal was to calculate the time it took to get from start to end date, only considering business hours. 

Step 2:

After your union step what you’ll want to do is generated rows to pad the days so you can calculate the time difference between start date (create date) and the end date from this you’ll be able to work out the resolution time for working hours. You do this by using the generate rows tool:

Created a column called: date padding and formatted the date to shows weekdays.

Following this, need a create a column called: date padding and formatted the date to shows weekdays, you do this by creating:

DateTimeFormat([date padding],“%A”)

Following this, you exclude the weekend using a custom filter :

[DatePAddingDay] != “Saturday”
AND
[DatePAddingDay] != “Sunday” 

In the True stream, you will want to create a column called ‘Time’ that pads time by minutes to calculate working hours between 8 AM – 6 PM you do this through a formula tool.

“2018-01-01 “+Right([date padding], 8)

Next, we filtered data to include the time between 8 AM – 6 PM:

[time] >= “2018-01-01 08:00:00”
and
[time] <= “2018-01-01 18:00:00”

Finally, we trimmed date padding to only include date without time so we can join with a separate excel input file that contains holiday dates because we wanted to exclude bank holidays from the working hours calculating.

DateTimeTrim([date padding],‘day’)

The final step for step 2 was joining the data with the excel holiday dates.

Step 3: 

We wanted time worked by ticket name so we grouped by ticket name and did a count of time in mins using the summarise tool, counting the time gave us the amount of time worked for working hours after this we simply converted our mins into hours using a formula tool.

[time to resolution (min)]/60

Lastly, we joined the working hours’ data to the original data using the join tool at this point we realised that tickets were worked on and resolved outside of working hours so we created a formula field that had: 0. This is to tag anything that was not matching on ticket name. We then used the union tool to bring unmatched tickets and the rest of our data.

Tada, you’re done!