SLA Calculation including weekends

Hi!

I was looking for ideal function to calculate SLA. There are post regarding SLA calculation only for Mon-Fr within operational hours.

My problem is how to calculate SLA when SLA is met within 12 hours.

Operational Hours:
Mon-Fr 8:00 - 20:00
Sat 09:00 - 18:00

Also including holidays.

For example:

1. Case 1 (SLA met)
Received 22/01/2019 17:54
Completed 23/01/2019 10:22

2. Case 1 (SLA met)
Received 18/01/2019 21:33
Completed 21/01/2019 08:11

3. Case 1 (SLA not met)
Received 20/01/2019 15:57
Completed 22/01/2019 12:12

3. Case 1 (SLA not met)
Received 20/01/2019 15:57
Completed 22/01/2019 12:12


Any help would be much appreciated.

I am not sure why 2 is classed as SLA Met -- given this spans the entire weekend this exceeds 12 hours (total duration is 18:11)

The attached has one approach but it's pretty "ugly" / brute force, and I'm sure someone will have a more succinct alternative that's beyond me at this point...
there used to be some folk on this board who were amazing at this type of calc - but they don't seem to be around any more...

Formula:

Please Login or Register  to view this content.

the above basically multiplies each day by either 9 or 12 hours depending on weekday (i.e. 9 for w/e), and then subtracts from that result the hours not worked on both start & end date {relative to respective weekday hours}.

note: I've attached a file to illustrate, this should auto translate the above to reflect your own locale (delimiters etc)

I need to calculate SLA based on operation hours.
In Case 2 we have 9 hours on Saturday and still 3 hours on Monday. That's why all cases received after 20:00 on Friday should be completed till 11:00 on Monday.

To plot holidays I need to add arrgument with range in Networkdays?

apologies, I misread the requirement -- I've revised the approach / formula per below -- and reattached for sake of download / translation

Formula:

Please Login or Register  to view this content.

as before there will be a better approach, but hopefully this helps you a little?

Thank you XLent. Works like a charm