I have been tasked with creating a report which will enable us to monitor our response times to customers and am struggling with how best to tackle this.
Each call placed is logged with a Start Date/Time value and then when it is closed a Close Date/Time is automatically assigned - straightforward so far?
I need to calculate how much time it takes us to resolve each call - i.e. the difference between the Start Date/Time and the Close Date/Time.
This would be fine except for the fact that I need to take into account that working hours are only 9:00am until 5:30pm Monday to Friday - so any time outside of what is considered our working week needs to be ignored in terms of the calculation.
Example, if a call is logged on 27/02/09 (i.e. a Friday) at 4:30pm and then resolved at 5:00pm on the same date, then this is fine as a simple calcualtion will work out that it took 30 minutes to resolve. However, if the same call was not resolved until 02/03/09 (i.e. the following Monday) at 10:15am, the simple calculation will assume that it has taken is 3945 minutes when it actually needs to result in 135 minutes.
I would be extremely grateful for any assistance you can offer!
IP Logged
