I am struggling to develop a formula to calculate response time for technical support calls at our company. Below are a few examples from my data set and the current formula I am using.
Incoming date/time Response date/time Total [h]:mm:ss
1/02/18 07:54:00 1/05/18 08:32:00 27:38:00
1/02/18 08:36:00 1/02/18 08:39:00 0:03:00
1/02/18 08:58:00 1/02/18 09:05:00 0:07:00
1/02/18 09:08:00 1/03/18 10:22:00 10:14:00
1/02/18 09:12:00 1/02/18 10:19:00 1:07:00
1/02/18 09:22:00 1/02/18 09:31:00 0:09:00
Formula - Current
R = incoming date and time for call
S = response date and time for call
W = work time end
V = work time start
=(NETWORKDAYS(R2,S2)-1)*($W$2-$V$2)+(MOD(S2,1)-MOD(R2,1))
The problem I am running into is calls can come in outside of the normal business hours set in W and V (8am-5pm) and on the weekends. Our support engineers sometimes show up early and respond to calls before 8am or stay late and respond to calls after 5pm. I want to calculate our response time for normal business hours, so if a call comes in on the weekend, the incoming time should be calculated for Monday at 8am start and end no matter what time it is for the response date/time. My current formula errors (with a negative) for the following scenario
Incoming date/time Response date/time
1/13/18 11:49:00 1/15/18 10:51:00
1/23/18 17:00:00 1/24/18 07:52:00
1/27/18 09:43:00 1/29/18 09:15:00
1/31/18 17:36:00 2/01/18 08:15:00
2/01/18 19:11:00 2/02/18 08:39:00
2/02/18 15:35:00 2/03/18 14:00:00
2/04/18 20:54:00 2/05/18 08:29:00
2/04/18 21:09:00 2/05/18 08:30:00
2/05/18 18:56:00 2/06/18 08:14:00
2/28/18 22:53:00 3/01/18 08:26:00
3/03/18 12:28:00 3/05/18 07:55:00
3/06/18 17:30:00 3/07/18 07:57:00
3/06/18 21:58:00 3/07/18 08:13:00
3/07/18 17:33:00 3/08/18 07:59:00
3/13/18 17:42:00 3/14/18 08:02:00
3/13/18 21:54:00 3/14/18 10:07:00
3/23/18 19:16:00 3/26/18 08:48:00
3/26/18 21:56:00 3/27/18 08:30:00
3/28/18 22:10:00 3/29/18 09:08:00
4/04/18 17:43:00 4/05/18 08:00:00
4/04/18 17:49:00 4/05/18 08:06:00
4/04/18 18:43:00 4/05/18 08:48:00
4/10/18 23:44:00 4/11/18 08:54:00
4/11/18 19:57:00 4/12/18 07:44:00
4/11/18 21:02:00 4/12/18 07:44:00
4/11/18 23:25:00 4/12/18 07:46:00
4/13/18 13:33:00 4/16/18 00:00:00
4/16/18 10:37:00 4/16/18 00:00:00
4/20/18 17:23:00 4/23/18 07:42:00
4/20/18 19:46:00 4/23/18 07:55:00
4/21/18 22:39:00 4/24/18 10:17:00
4/25/18 21:58:00 4/26/18 09:55:00
4/28/18 16:57:00 4/30/18 11:33:00
4/30/18 23:18:00 5/01/18 10:32:00
5/03/18 17:09:00 5/04/18 08:03:00
5/10/18 16:57:00 5/11/18 07:38:00
5/12/18 14:41:00 5/14/18 07:56:00
5/31/18 20:04:00 6/01/18 08:20:00
5/31/18 21:18:00 6/01/18 08:31:00
6/04/18 12:01:00 6/04/18 12:00:00
6/07/18 17:49:00 6/08/18 08:10:00
6/11/18 18:14:00 6/12/18 08:26:00
6/12/18 23:51:00 6/13/18 08:03:00
6/24/18 21:35:00 6/25/18 07:53:00
6/24/18 22:00:00 6/25/18 08:08:00
6/24/18 22:14:00 6/25/18 08:09:00
Any advice would be much appreciated on this!
EDIT: I have added the Excel workbook I am working on with examples of correct and incorrect values.
Bookmarks