JavaScript is disabled. For a better experience, please enable JavaScript in your browser before proceeding.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an
alternative browser .
How to calculate Average Handling Time (AHT
Hello everyone,
Hope you are having a great week.
I am trying to figure out a way to calculate AHT (Average Handling time) for each cases irrespective of the dates. Just to summarize the table, DE stands for Data Entry. Average handling time is calculated as the total time taken to complete a specific case (for instance, AHT for Case 1 is 3.27 hrs). In situations where a specific case extends to the next day (e.g. Case 2 & Case 5 in the below table) there has to be a formula to calculate the AHTs in such cases.
Can anybody help me calculate AHT for the below table? It should be case specific
Thanks in advance.
Case Task
Start date (mm/dd/yy)
Start time (hh:mm)
End Date (mm/dd/yy)
End time (hh:mm)
Time (in hrs)
AHT
Status
Case 1 DE
09-24-19
09:25:00
09-24-19
12:52:00
3:27
Completed
Case 2 DE
09-24-19
12:54:00
09-24-19
13:01:00
0:07
In Progress
DE
09-24-19
13:52:00
09-24-19
15:39:00
1:47
In Progress
Meeting
09-24-19
15:39:00
09-24-19
17:33:00
1:54
Completed
DE
09-25-19
09:12:00
09-25-19
10:35:00
1:23
Completed
Case 3 DE
09-25-19
10:50:00
09-25-19
12:48:00
1:58
Completed
Case 4 DE
09-25-19
12:49:00
09-25-19
13:01:00
0:12
In Progress
DE
09-25-19
13:47:00
09-25-19
14:59:00
1:12
Completed
Case 5 DE
09-25-19
15:05:00
09-25-19
15:20:00
0:15
In Progress
Query Resolution
09-25-19
15:20:00
09-25-19
15:45:00
0:25
Completed
DE
09-25-19
15:46:00
09-25-19
16:30:00
0:44
In Progress
Query Resolution
09-25-19
16:31:00
09-25-19
16:37:00
0:06
Completed
DE
09-25-19
16:37:00
09-25-19
17:28:00
0:51
In Progress
DE
09-26-19
09:13:00
09-26-19
09:48:00
0:35
In Progress
Working on Feedback
09-26-19
09:48:00
09-26-19
12:23:00
2:35
In Progress
DE
09-26-19
12:23:00
09-26-19
13:01:00
0:38
In Progress
DE
09-26-19
13:48:00
09-26-19
14:24:00
0:36
In Progress
Meeting
09-26-19
14:24:00
09-26-19
15:24:00
1:00
Completed
DE
09-26-19
15:24:00
09-26-19
16:51:00
1:27
Completed
<tbody>
</tbody>
Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Hi,
The each last case entry seems to be returning the total hours, can you use this?
Book1 A B C D E F G H I 1 Case Task Start date (mm/dd/yy) Start time (hh:mm) End Date (mm/dd/yy) End time (hh:mm) Time (in hrs) AHT Status 2 Case 1 DE 24-09-19 9:25:00 24-09-19 12:52:00 3:27 3:27 Completed 3 Case 2 DE 24-09-19 12:54:00 24-09-19 13:01:00 0:07 0:07 In Progress 4 Case 2 DE 24-09-19 13:52:00 24-09-19 15:39:00 1:47 1:54 In Progress 5 Case 2 Meeting 24-09-19 15:39:00 24-09-19 17:33:00 1:54 3:48 Completed 6 Case 2 DE 25-09-19 9:12:00 25-09-19 10:35:00 1:23 5:11 Completed 7 Case 3 DE 25-09-19 10:50:00 25-09-19 12:48:00 1:58 1:58 Completed 8 Case 4 DE 25-09-19 12:49:00 25-09-19 13:01:00 0:12 0:12 In Progress 9 Case 4 DE 25-09-19 13:47:00 25-09-19 14:59:00 1:12 1:24 Completed 10 Case 5 DE 25-09-19 15:05:00 25-09-19 15:20:00 0:15 0:15 In Progress 11 Case 5 Query Resolution 25-09-19 15:20:00 25-09-19 15:45:00 0:25 0:40 Completed 12 Case 5 DE 25-09-19 15:46:00 25-09-19 16:30:00 0:44 1:24 In Progress 13 Case 5 Query Resolution 25-09-19 16:31:00 25-09-19 16:37:00 0:06 1:30 Completed 14 Case 5 DE 25-09-19 16:37:00 25-09-19 17:28:00 0:51 2:21 In Progress 15 Case 5 DE 26-09-19 9:13:00 26-09-19 9:48:00 0:35 2:56 In Progress 16 Case 5 Working on Feedback 26-09-19 9:48:00 26-09-19 12:23:00 2:35 5:31 In Progress 17 Case 5 DE 26-09-19 12:23:00 26-09-19 13:01:00 0:38 6:09 In Progress 18 Case 5 DE 26-09-19 13:48:00 26-09-19 14:24:00 0:36 6:45 In Progress 19 Case 5 Meeting 26-09-19 14:24:00 26-09-19 15:24:00 1:00 7:45 Completed 20 Case 5 DE 26-09-19 15:24:00 26-09-19 16:51:00 1:27 9:12 Completed
Hi RasGhul,
Thanks for the response.
I have mentioned the expected results for AHT in the below table for your reference.
Also, I would like to inform you that AHT has to be calculated only for Data Entry (DE) tasks when a specific case is "completed" in status column and additional tasks such as meeting, query resolution etc has to be excluded from AHT calculation. For instance by calculating manually AHT for Case 1 is 3:27, AHT for Case 2 is 3:24, AHT for case 5 is 5:06 etc.
Can you please assist?
Case No.
Task
Start date (mm/dd/yy)
Start time (hh:mm)
End Date (mm/dd/yy)
End time (hh:mm)
Time (in hrs)
Status
AHT
Case 1
DE
09-24-19
09:25:00
09-24-19
12:52:00
3:27
Completed
03:27
Case 2
DE
09-24-19
12:54:00
09-24-19
13:01:00
0:07
In Progress
DE
09-24-19
13:52:00
09-24-19
15:39:00
1:47
In Progress
Meeting
09-24-19
15:39:00
09-24-19
17:33:00
1:54
Completed
DE
09-25-19
09:12:00
09-25-19
10:35:00
1:23
Completed
03:24
Case 3
DE
09-25-19
10:50:00
09-25-19
12:48:00
1:58
Completed
01:58
Case 4
DE
09-25-19
12:49:00
09-25-19
13:01:00
0:12
In Progress
DE
09-25-19
13:47:00
09-25-19
14:59:00
1:12
Completed
01:24
Case 5
DE
09-25-19
15:05:00
09-25-19
15:20:00
0:15
In Progress
Query Resolution
09-25-19
15:20:00
09-25-19
15:45:00
0:25
Completed
DE
09-25-19
15:46:00
09-25-19
16:30:00
0:44
In Progress
Query Resolution
09-25-19
16:31:00
09-25-19
16:37:00
0:06
Completed
DE
09-25-19
16:37:00
09-25-19
17:28:00
0:51
In Progress
DE
09-26-19
09:13:00
09-26-19
09:48:00
0:35
In Progress
Working on Feedback
09-26-19
09:48:00
09-26-19
12:23:00
2:35
Completed
DE
09-26-19
12:23:00
09-26-19
13:01:00
0:38
In Progress
DE
09-26-19
13:48:00
09-26-19
14:24:00
0:36
In Progress
Meeting
09-26-19
14:24:00
09-26-19
15:24:00
1:00
Completed
DE
09-26-19
15:24:00
09-26-19
16:51:00
1:27
Completed
05:06
Case 6
DE
09-26-19
16:52:00
09-26-19
17:23:00
0:31
In Progress
Other
09-26-19
17:23:00
09-26-19
17:31:00
0:08
Completed
DE
09-27-19
09:20:00
09-27-19
11:54:00
2:34
In Progress
Query Resolution
09-27-19
11:54:00
09-27-19
12:35:00
0:41
Completed
DE
09-27-19
12:35:00
09-27-19
13:01:00
0:26
Completed
03:31
Case 7
DE
09-27-19
16:57:00
09-27-19
17:30:00
00:33
In Progress
DE
09-27-19
13:51:00
09-27-19
16:06:00
2:15
In Progress
DE
09-30-19
09:11:00
09-30-19
10:28:00
1:17
In Progress
Query Resolution
09-30-19
10:29:00
09-30-19
10:49:00
0:20
Completed
DE
09-30-19
10:49:00
09-30-19
10:56:00
0:07
In Progress
Meeting
09-30-19
10:56:00
09-30-19
11:09:00
0:13
Completed
DE
09-30-19
11:10:00
09-30-19
12:13:00
1:03
In Progress
Other
09-30-19
12:13:00
09-30-19
12:22:00
0:09
Completed
DE
09-30-19
12:23:00
09-30-19
12:57:00
0:34
Completed
05:49
Case 8
DE
09-30-19
13:44:00
09-30-19
15:49:00
2:05
Completed
02:05
Case 9
DE
09-30-19
15:50:00
09-30-19
17:30:00
1:40
In Progress
DE
10-01-19
09:14:00
10-01-19
11:09:00
1:55
Completed
03:35
<tbody>
</tbody>
Ok,
I had to use a helper column for the sumifs to calculate properly, I don't like hard coding the "DE" & "COMPLETED" criteria into formulas but this seems to be working now;
Book1 A B C D E F G H I J 1 Case No. Task Start date (mm/dd/yy) Start time (hh:mm) End Date (mm/dd/yy) End time (hh:mm) Time (in hrs) Status AHT Case Fill 2 Case 1 DE 09-24-19 9:25:00 09-24-19 12:52:00 3:27 Completed 3:27 Case 1 3 Case 2 DE 09-24-19 12:54:00 09-24-19 13:01:00 0:07 In Progress Case 2 4 DE 09-24-19 13:52:00 09-24-19 15:39:00 1:47 In Progress Case 2 5 Meeting 09-24-19 15:39:00 09-24-19 17:33:00 1:54 Completed Case 2 6 DE 09-25-19 9:12:00 09-25-19 10:35:00 1:23 Completed 3:17 Case 2 7 Case 3 DE 09-25-19 10:50:00 09-25-19 12:48:00 1:58 Completed 1:58 Case 3 8 Case 4 DE 09-25-19 12:49:00 09-25-19 13:01:00 0:12 In Progress Case 4 9 DE 09-25-19 13:47:00 09-25-19 14:59:00 1:12 Completed 1:24 Case 4 10 Case 5 DE 09-25-19 15:05:00 09-25-19 15:20:00 0:15 In Progress Case 5 11 Query Resolution 09-25-19 15:20:00 09-25-19 15:45:00 0:25 Completed Case 5 12 DE 09-25-19 15:46:00 09-25-19 16:30:00 0:44 In Progress Case 5 13 Query Resolution 09-25-19 16:31:00 09-25-19 16:37:00 0:06 Completed Case 5 14 DE 09-25-19 16:37:00 09-25-19 17:28:00 0:51 In Progress Case 5 15 DE 09-26-19 9:13:00 09-26-19 9:48:00 0:35 In Progress Case 5 16 Working on Feedback 09-26-19 9:48:00 09-26-19 12:23:00 2:35 Completed Case 5 17 DE 09-26-19 12:23:00 09-26-19 13:01:00 0:38 In Progress Case 5 18 DE 09-26-19 13:48:00 09-26-19 14:24:00 0:36 In Progress Case 5 19 Meeting 09-26-19 14:24:00 09-26-19 15:24:00 1:00 Completed Case 5 20 DE 09-26-19 15:24:00 09-26-19 16:51:00 1:27 Completed 5:06 Case 5 21 Case 6 DE 09-26-19 16:52:00 09-26-19 17:23:00 0:31 In Progress Case 6 22 Other 09-26-19 17:23:00 09-26-19 17:31:00 0:08 Completed Case 6 23 DE 09-27-19 9:20:00 09-27-19 11:54:00 2:34 In Progress Case 6 24 Query Resolution 09-27-19 11:54:00 09-27-19 12:35:00 0:41 Completed Case 6 25 DE 09-27-19 12:35:00 09-27-19 13:01:00 0:26 Completed 3:31 Case 6 26 Case 7 DE 09-27-19 16:57:00 09-27-19 17:30:00 0:33 In Progress Case 7 27 DE 09-27-19 13:51:00 09-27-19 16:06:00 2:15 In Progress Case 7 28 DE 09-30-19 9:11:00 09-30-19 10:28:00 1:17 In Progress Case 7 29 Query Resolution 09-30-19 10:29:00 09-30-19 10:49:00 0:20 Completed Case 7 30 DE 09-30-19 10:49:00 09-30-19 10:56:00 0:07 In Progress Case 7 31 Meeting 09-30-19 10:56:00 09-30-19 11:09:00 0:13 Completed Case 7 32 DE 09-30-19 11:10:00 09-30-19 12:13:00 1:03 In Progress Case 7 33 Other 09-30-19 12:13:00 09-30-19 12:22:00 0:09 Completed Case 7 34 DE 09-30-19 12:23:00 09-30-19 12:57:00 0:34 Completed 5:49 Case 7 35 Case 8 DE 09-30-19 13:44:00 09-30-19 15:49:00 2:05 Completed 2:05 Case 8 36 Case 9 DE 09-30-19 15:50:00 09-30-19 17:30:00 1:40 In Progress Case 9 37 DE 10-01-19 9:14:00 10-01-19 11:09:00 1:55 Completed 3:35 Case 9
Ok,
I had to use a helper column for the sumifs to calculate properly, I don't like hard coding the "DE" & "COMPLETED" criteria into formulas but this seems to be working now;
Book1 A B C D E F G H I J 1 Case No. Task Start date (mm/dd/yy) Start time (hh:mm) End Date (mm/dd/yy) End time (hh:mm) Time (in hrs) Status AHT Case Fill 2 Case 1 DE 09-24-19 9:25:00 09-24-19 12:52:00 3:27 Completed 3:27 Case 1 3 Case 2 DE 09-24-19 12:54:00 09-24-19 13:01:00 0:07 In Progress Case 2 4 DE 09-24-19 13:52:00 09-24-19 15:39:00 1:47 In Progress Case 2 5 Meeting 09-24-19 15:39:00 09-24-19 17:33:00 1:54 Completed Case 2 6 DE 09-25-19 9:12:00 09-25-19 10:35:00 1:23 Completed 3:17 Case 2 7 Case 3 DE 09-25-19 10:50:00 09-25-19 12:48:00 1:58 Completed 1:58 Case 3 8 Case 4 DE 09-25-19 12:49:00 09-25-19 13:01:00 0:12 In Progress Case 4 9 DE 09-25-19 13:47:00 09-25-19 14:59:00 1:12 Completed 1:24 Case 4 10 Case 5 DE 09-25-19 15:05:00 09-25-19 15:20:00 0:15 In Progress Case 5 11 Query Resolution 09-25-19 15:20:00 09-25-19 15:45:00 0:25 Completed Case 5 12 DE 09-25-19 15:46:00 09-25-19 16:30:00 0:44 In Progress Case 5 13 Query Resolution 09-25-19 16:31:00 09-25-19 16:37:00 0:06 Completed Case 5 14 DE 09-25-19 16:37:00 09-25-19 17:28:00 0:51 In Progress Case 5 15 DE 09-26-19 9:13:00 09-26-19 9:48:00 0:35 In Progress Case 5 16 Working on Feedback 09-26-19 9:48:00 09-26-19 12:23:00 2:35 Completed Case 5 17 DE 09-26-19 12:23:00 09-26-19 13:01:00 0:38 In Progress Case 5 18 DE 09-26-19 13:48:00 09-26-19 14:24:00 0:36 In Progress Case 5 19 Meeting 09-26-19 14:24:00 09-26-19 15:24:00 1:00 Completed Case 5 20 DE 09-26-19 15:24:00 09-26-19 16:51:00 1:27 Completed 5:06 Case 5
Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin