SLA Calculations

Hello all,

I want to write a formula to calculate the SLA for our outsourcing agent. The formula is to calulate the time it took the agent to resolve a machine. The SLA is calculated from the time the ticket is opened until it is resolved based on the below criteria:

1- Working Hours are from 06:00 am until 00:00 am everyday. 24/7, so there are no weekends or holidays
2- Tickets can be opened during any time of the day including none working hours.
3- A ticket can remain open for a number of days, example, it can be opened on 01-Oct-2015 10:00:00 and closed on 03-Oct-2015 13:00:00
4- Calculation should exclude the time of non working hours (that is from midnight until 6:00 am) for all tickets.

What is the best formula I can use?

Thanks a lot.
BJ

G6 contains start date and time, H6 contains end date and time.
Format the cell as custom format [h]:mm or [hh]:mm to see hours and minutes over 24 hours
=0.75-MAX(MOD(G6,1),0.25)+MAX(MOD(H6,1),0.25)+0.75*(INT(H6)-INT(G6)-1)

I can't help thinking there must be a more elegant solution.

Excellent!! this is exactly what I was looking for. I tried different combinations and they all worked perfectly.

Thanks a lot p45cal )

Since posting I put the same question on another forum and tested several suggestions including the one above which proved to give wrong results:
Thu, October 01, 2015 21:00 to Sun, October 04, 2015 00:00 should give 39 hours but gave 57 hours
Fri, October 02, 2015 00:00 to Sun, October 04, 2015 03:00 should give 36 hours but gave 18 hours

but this one:
=MIN(INT(G6+1)-G6,0.75) + MAX(H6-INT(H6)-0.25,0) + (INT(H6)-INT(G6+1))*0.75
by Zoran Stanojević seems to give the correct results.

Hello P45cal,

I tried both formulas and both gave the same result. I have also tried the above scenarios on both formulas, both gave me the same number of correct hours (Below). I'll keep them both handy and use them in the future sheets. If I get any discrepancies I'll send you a note.

Ha! Revisiting this, when I enter the times manually, both formulae give the same result. The trouble was that in my testbed I had used a formula to generate test start and end times, then converted the results to hard values. The formula I used was to add 1/24 to successive rows (1hour), the result was that the values must be very slightly different (maybe because 1/24 is a recurring decimal (the difference doesn't even show up if you subtract the values from each other)) and MOD(H6,1) gave different results.
Where Zoran's formula is better is that it doesn't use MOD at all and so properly deals with the tiny differences.
So if you only enter dates manually this won't matter.