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
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