How to calculate Average Handling Time (AHT

El chapo

New Member
Joined
Nov 13, 2019
Messages
2
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.


CaseTask
Start date (mm/dd/yy)
Start time (hh:mm)
End Date (mm/dd/yy)
End time (hh:mm)
Time (in hrs)
AHT
Status
Case 1DE
09-24-19
09:25:00
09-24-19
12:52:00
3:27

Completed
Case 2DE
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 3DE
09-25-19
10:50:00
09-25-19
12:48:00
1:58

Completed
Case 4DE
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 5DE
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>
 

Excel Facts

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
ABCDEFGHI
1CaseTaskStart date (mm/dd/yy)Start time (hh:mm)End Date (mm/dd/yy)End time (hh:mm)Time (in hrs)AHTStatus
2Case 1DE24-09-199:25:0024-09-1912:52:003:273:27Completed
3Case 2DE24-09-1912:54:0024-09-1913:01:000:070:07In Progress
4Case 2DE24-09-1913:52:0024-09-1915:39:001:471:54In Progress
5Case 2Meeting24-09-1915:39:0024-09-1917:33:001:543:48Completed
6Case 2DE25-09-199:12:0025-09-1910:35:001:235:11Completed
7Case 3DE25-09-1910:50:0025-09-1912:48:001:581:58Completed
8Case 4DE25-09-1912:49:0025-09-1913:01:000:120:12In Progress
9Case 4DE25-09-1913:47:0025-09-1914:59:001:121:24Completed
10Case 5DE25-09-1915:05:0025-09-1915:20:000:150:15In Progress
11Case 5Query Resolution25-09-1915:20:0025-09-1915:45:000:250:40Completed
12Case 5DE25-09-1915:46:0025-09-1916:30:000:441:24In Progress
13Case 5Query Resolution25-09-1916:31:0025-09-1916:37:000:061:30Completed
14Case 5DE25-09-1916:37:0025-09-1917:28:000:512:21In Progress
15Case 5DE26-09-199:13:0026-09-199:48:000:352:56In Progress
16Case 5Working on Feedback26-09-199:48:0026-09-1912:23:002:355:31In Progress
17Case 5DE26-09-1912:23:0026-09-1913:01:000:386:09In Progress
18Case 5DE26-09-1913:48:0026-09-1914:24:000:366:45In Progress
19Case 5Meeting26-09-1914:24:0026-09-1915:24:001:007:45Completed
20Case 5DE26-09-1915:24:0026-09-1916:51:001:279:12Completed
Sheet1
Cell Formulas
RangeFormula
H2=SUMIFS($G$2:G2,$A$2:A2,$A2)
 
Upvote 0
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>
 
Upvote 0
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
ABCDEFGHIJ
1Case No.TaskStart date (mm/dd/yy)Start time (hh:mm)End Date (mm/dd/yy)End time (hh:mm)Time (in hrs)StatusAHTCase Fill
2Case 1DE09-24-199:25:0009-24-1912:52:003:27Completed3:27Case 1
3Case 2DE09-24-1912:54:0009-24-1913:01:000:07In ProgressCase 2
4DE09-24-1913:52:0009-24-1915:39:001:47In ProgressCase 2
5Meeting09-24-1915:39:0009-24-1917:33:001:54CompletedCase 2
6DE09-25-199:12:0009-25-1910:35:001:23Completed3:17Case 2
7Case 3DE09-25-1910:50:0009-25-1912:48:001:58Completed1:58Case 3
8Case 4DE09-25-1912:49:0009-25-1913:01:000:12In ProgressCase 4
9DE09-25-1913:47:0009-25-1914:59:001:12Completed1:24Case 4
10Case 5DE09-25-1915:05:0009-25-1915:20:000:15In ProgressCase 5
11Query Resolution09-25-1915:20:0009-25-1915:45:000:25CompletedCase 5
12DE09-25-1915:46:0009-25-1916:30:000:44In ProgressCase 5
13Query Resolution09-25-1916:31:0009-25-1916:37:000:06CompletedCase 5
14DE09-25-1916:37:0009-25-1917:28:000:51In ProgressCase 5
15DE09-26-199:13:0009-26-199:48:000:35In ProgressCase 5
16Working on Feedback09-26-199:48:0009-26-1912:23:002:35CompletedCase 5
17DE09-26-1912:23:0009-26-1913:01:000:38In ProgressCase 5
18DE09-26-1913:48:0009-26-1914:24:000:36In ProgressCase 5
19Meeting09-26-1914:24:0009-26-1915:24:001:00CompletedCase 5
20DE09-26-1915:24:0009-26-1916:51:001:27Completed5:06Case 5
21Case 6DE09-26-1916:52:0009-26-1917:23:000:31In ProgressCase 6
22Other09-26-1917:23:0009-26-1917:31:000:08CompletedCase 6
23DE09-27-199:20:0009-27-1911:54:002:34In ProgressCase 6
24Query Resolution09-27-1911:54:0009-27-1912:35:000:41CompletedCase 6
25DE09-27-1912:35:0009-27-1913:01:000:26Completed3:31Case 6
26Case 7DE09-27-1916:57:0009-27-1917:30:000:33In ProgressCase 7
27DE09-27-1913:51:0009-27-1916:06:002:15In ProgressCase 7
28DE09-30-199:11:0009-30-1910:28:001:17In ProgressCase 7
29Query Resolution09-30-1910:29:0009-30-1910:49:000:20CompletedCase 7
30DE09-30-1910:49:0009-30-1910:56:000:07In ProgressCase 7
31Meeting09-30-1910:56:0009-30-1911:09:000:13CompletedCase 7
32DE09-30-1911:10:0009-30-1912:13:001:03In ProgressCase 7
33Other09-30-1912:13:0009-30-1912:22:000:09CompletedCase 7
34DE09-30-1912:23:0009-30-1912:57:000:34Completed5:49Case 7
35Case 8DE09-30-1913:44:0009-30-1915:49:002:05Completed2:05Case 8
36Case 9DE09-30-1915:50:0009-30-1917:30:001:40In ProgressCase 9
37DE10-01-199:14:0010-01-1911:09:001:55Completed3:35Case 9
Sheet1
Cell Formulas
RangeFormula
I2=IF(A2&B2&H2=A2&"DE"&"COMPLETED",SUMIFS($G$2:G2,$J$2:J2,J2,$B$2:B2,"DE"),"")
J2=IF(A2>0,A2,IF(A2="",J1,IF(J1<>A2,A2)))
 
Upvote 0
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
ABCDEFGHIJ
1Case No.TaskStart date (mm/dd/yy)Start time (hh:mm)End Date (mm/dd/yy)End time (hh:mm)Time (in hrs)StatusAHTCase Fill
2Case 1DE09-24-199:25:0009-24-1912:52:003:27Completed3:27Case 1
3Case 2DE09-24-1912:54:0009-24-1913:01:000:07In ProgressCase 2
4DE09-24-1913:52:0009-24-1915:39:001:47In ProgressCase 2
5Meeting09-24-1915:39:0009-24-1917:33:001:54CompletedCase 2
6DE09-25-199:12:0009-25-1910:35:001:23Completed3:17Case 2
7Case 3DE09-25-1910:50:0009-25-1912:48:001:58Completed1:58Case 3
8Case 4DE09-25-1912:49:0009-25-1913:01:000:12In ProgressCase 4
9DE09-25-1913:47:0009-25-1914:59:001:12Completed1:24Case 4
10Case 5DE09-25-1915:05:0009-25-1915:20:000:15In ProgressCase 5
11Query Resolution09-25-1915:20:0009-25-1915:45:000:25CompletedCase 5
12DE09-25-1915:46:0009-25-1916:30:000:44In ProgressCase 5
13Query Resolution09-25-1916:31:0009-25-1916:37:000:06CompletedCase 5
14DE09-25-1916:37:0009-25-1917:28:000:51In ProgressCase 5
15DE09-26-199:13:0009-26-199:48:000:35In ProgressCase 5
16Working on Feedback09-26-199:48:0009-26-1912:23:002:35CompletedCase 5
17DE09-26-1912:23:0009-26-1913:01:000:38In ProgressCase 5
18DE09-26-1913:48:0009-26-1914:24:000:36In ProgressCase 5
19Meeting09-26-1914:24:0009-26-1915:24:001:00CompletedCase 5
20DE09-26-1915:24:0009-26-1916:51:001:27Completed5:06Case 5
Sheet1
Cell Formulas
RangeFormula
I2I2=IF(A2&B2&H2=A2&"DE"&"COMPLETED",SUMIFS($G$2:G2,$J$2:J2,J2,$B$2:B2,"DE"),"")
J2J2=IF(A2>0,A2,IF(A2="",J1,IF(J1<>A2,A2)))
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

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

Which adblocker are you using?

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