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

#### dfo222

##### New Member
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

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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"))

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

Replies
32
Views
456
Replies
3
Views
207
Replies
15
Views
892
Replies
3
Views
130
Replies
8
Views
361

Threads
1,196,183
Messages
6,013,914
Members
441,795
Latest member
Lilium

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back