Calculating if SLA response time is met, using blocks of time

[dfo222]

Hi,
I've found some great solutions to working out response times in this forum, but I have a slightly more complicated SLA and wonder if there's a way to do this.

Here's the SLA we have:

If a ticket is created between 09.00 and 13.00, response needs to be between 13.00 and 17.00 the same day.
If a ticket is created between 13.00 and 17.00, response needs to be between 09.00 and 13.00 the next working day (Monday - Friday).

Both the ticket creation time and the response time might be outside of the hours 09.00 - 17.00.
If a ticket is created between 17.00 and 09.00, it counts as having been created at 09.00 on the next working day.
Similarly a response between 17.00 and 09.00 counts as having been happened at 09.00 on the next working day.
Sometimes creation happens after 17.00, and the response comes before 09.00 the next day, meaning the response time is technically 0.

I have the creation time and first response time in this format:
28/05/2015 16:59:00 (dd/mm/yyyy hh:mm)


I don't need to know the actual response time in hours (and I've found a formula here: http://www.mrexcel.com/forum/excel-questions/467814-calculating-response-time.html for working that out).

I just need to know if the SLA response time agreement was met or not.

Any ideas on how to calculate this? I'm not sure where to start.

Thanks,
Daniel

 

[barry houdini]

Hello Daniel, welcome to MrExcel

If you have creation time/date in A2 and response time/date in B2 use this formula in C2 to get "Met" or "Not Met"

=IF(B2="","",IF(B2<=IF(AND(NETWORKDAYS(A2,A2),MOD(A2,1)<="17:00"+0),IF(MOD(A2,1)<="13:00"+0,INT(A2)+"17:00",WORKDAY(A2,1)+"13:00"),WORKDAY(A2,1)+"17:00"),"Met","Not Met"))

 

[dfo222]

That's fantastic, works perfectly.
Many thanks for the quick solution.