SLA/Turnaround Time

[BI_Noob_Forgiveme]

Hi All,

I’m new here and hoping to get some assistance.

For background, I work in finance and look after a small but busy team. The work we do is based on emails received by customers which we then have to fulfill. We have SLA’s for our different work queues. We don’t work weekends or public holidays and our hours of operation is from 9am – 5pm (which is when the SLA clock stops).

What do I need?
Based on the above information, I need a formula or way of working out the TAT (Turnaround time).

Date & Time Received Date & Time closed
24/08/2020 9:15 26/08/2020 11:30
21/08/2020 12:30 24/08/2020 6:30

Example 1. The turnaround time is 18 hours and 15 mins

Example 2. The turnaround time is 4 hours 30 minutes. This is because we received the file on a Friday and closed the case on the following Monday at 6:30am (clock does not start until 9am).

Hope this makes sense and hope I’ve added the post in the right section. I do have a copy of the file with dummy data.

Warm regards,
Sean aka BI_Noob

[Melissa]

Hi @BI_Noob_Forgiveme,

I think you’ll find what you need in this thread.

Time format 37:30:55 greater than 24 hours

. Okay so Bill Szysz created a Power Query M function that does just that and here it is: (WHStart, WHEnd, Start, End, ListOfHolidays) => let DStart = Number.From(DateTime.Date(Start)), // start day TStart = Number.From(DateTime.Time(Start)), // start time DEnd = Number.From(DateTime.Date(End)), // end day TEnd = Number.From(DateTime.Time(End)), // end time // List of days without saturdays and sundays ListOfNumbe…

.
If you need further assitance please provide mock up data and PBIX, thanks
In the Reply window, you can use this icon to upload a file.

I hope this is helpful.

[BI_Noob_Forgiveme]

Thank you @Melissa. Will review and come back to you. Thanks also for the tip on uploading the file.