How to calculate Average Handling Time (AHT

E

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.


[TABLE="width: 0"]
<tbody>[TR]
[TD]Case[/TD]
[TD]Task
[/TD]
[TD]Start date (mm/dd/yy)
[/TD]
[TD]Start time (hh:mm)
[/TD]
[TD]End Date (mm/dd/yy)
[/TD]
[TD]End time (hh:mm)
[/TD]
[TD]Time (in hrs)
[/TD]
[TD]AHT
[/TD]
[TD]Status
[/TD]
[/TR]
[TR]
[TD]Case 1[/TD]
[TD]DE
[/TD]
[TD]09-24-19
[/TD]
[TD]09:25:00
[/TD]
[TD]09-24-19
[/TD]
[TD]12:52:00
[/TD]
[TD]3:27
[/TD]
[TD]
[/TD]
[TD]Completed
[/TD]
[/TR]
[TR]
[TD]Case 2[/TD]
[TD]DE
[/TD]
[TD]09-24-19
[/TD]
[TD]12:54:00
[/TD]
[TD]09-24-19
[/TD]
[TD]13:01:00
[/TD]
[TD]0:07
[/TD]
[TD]
[/TD]
[TD]In Progress
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]DE
[/TD]
[TD]09-24-19
[/TD]
[TD]13:52:00
[/TD]
[TD]09-24-19
[/TD]
[TD]15:39:00
[/TD]
[TD]1:47
[/TD]
[TD]
[/TD]
[TD]In Progress
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]Meeting
[/TD]
[TD]09-24-19
[/TD]
[TD]15:39:00
[/TD]
[TD]09-24-19
[/TD]
[TD]17:33:00
[/TD]
[TD]1:54
[/TD]
[TD]
[/TD]
[TD]Completed
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]DE
[/TD]
[TD]09-25-19
[/TD]
[TD]09:12:00
[/TD]
[TD]09-25-19
[/TD]
[TD]10:35:00
[/TD]
[TD]1:23
[/TD]
[TD]
[/TD]
[TD]Completed
[/TD]
[/TR]
[TR]
[TD]Case 3[/TD]
[TD]DE
[/TD]
[TD]09-25-19
[/TD]
[TD]10:50:00
[/TD]
[TD]09-25-19
[/TD]
[TD]12:48:00
[/TD]
[TD]1:58
[/TD]
[TD]
[/TD]
[TD]Completed
[/TD]
[/TR]
[TR]
[TD]Case 4[/TD]
[TD]DE
[/TD]
[TD]09-25-19
[/TD]
[TD]12:49:00
[/TD]
[TD]09-25-19
[/TD]
[TD]13:01:00
[/TD]
[TD]0:12
[/TD]
[TD]
[/TD]
[TD]In Progress
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]DE
[/TD]
[TD]09-25-19
[/TD]
[TD]13:47:00
[/TD]
[TD]09-25-19
[/TD]
[TD]14:59:00
[/TD]
[TD]1:12
[/TD]
[TD]
[/TD]
[TD]Completed
[/TD]
[/TR]
[TR]
[TD]Case 5[/TD]
[TD]DE
[/TD]
[TD]09-25-19
[/TD]
[TD]15:05:00
[/TD]
[TD]09-25-19
[/TD]
[TD]15:20:00
[/TD]
[TD]0:15
[/TD]
[TD]
[/TD]
[TD]In Progress
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]Query Resolution
[/TD]
[TD]09-25-19
[/TD]
[TD]15:20:00
[/TD]
[TD]09-25-19
[/TD]
[TD]15:45:00
[/TD]
[TD]0:25
[/TD]
[TD]
[/TD]
[TD]Completed
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]DE
[/TD]
[TD]09-25-19
[/TD]
[TD]15:46:00
[/TD]
[TD]09-25-19
[/TD]
[TD]16:30:00
[/TD]
[TD]0:44
[/TD]
[TD]
[/TD]
[TD]In Progress
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]Query Resolution
[/TD]
[TD]09-25-19
[/TD]
[TD]16:31:00
[/TD]
[TD]09-25-19
[/TD]
[TD]16:37:00
[/TD]
[TD]0:06
[/TD]
[TD]
[/TD]
[TD]Completed
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]DE
[/TD]
[TD]09-25-19
[/TD]
[TD]16:37:00
[/TD]
[TD]09-25-19
[/TD]
[TD]17:28:00
[/TD]
[TD]0:51
[/TD]
[TD]
[/TD]
[TD]In Progress
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]DE
[/TD]
[TD]09-26-19
[/TD]
[TD]09:13:00
[/TD]
[TD]09-26-19
[/TD]
[TD]09:48:00
[/TD]
[TD]0:35
[/TD]
[TD]
[/TD]
[TD]In Progress
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]Working on Feedback
[/TD]
[TD]09-26-19
[/TD]
[TD]09:48:00
[/TD]
[TD]09-26-19
[/TD]
[TD]12:23:00
[/TD]
[TD]2:35
[/TD]
[TD]
[/TD]
[TD]In Progress
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]DE
[/TD]
[TD]09-26-19
[/TD]
[TD]12:23:00
[/TD]
[TD]09-26-19
[/TD]
[TD]13:01:00
[/TD]
[TD]0:38
[/TD]
[TD]
[/TD]
[TD]In Progress
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]DE
[/TD]
[TD]09-26-19
[/TD]
[TD]13:48:00
[/TD]
[TD]09-26-19
[/TD]
[TD]14:24:00
[/TD]
[TD]0:36
[/TD]
[TD]
[/TD]
[TD]In Progress
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]Meeting
[/TD]
[TD]09-26-19
[/TD]
[TD]14:24:00
[/TD]
[TD]09-26-19
[/TD]
[TD]15:24:00
[/TD]
[TD]1:00
[/TD]
[TD]
[/TD]
[TD]Completed
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]DE
[/TD]
[TD]09-26-19
[/TD]
[TD]15:24:00
[/TD]
[TD]09-26-19
[/TD]
[TD]16:51:00
[/TD]
[TD]1:27
[/TD]
[TD]
[/TD]
[TD]Completed
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
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?

[TABLE="width: 0"]
<tbody>[TR]
[TD]Case No.
[/TD]
[TD]Task
[/TD]
[TD]Start date (mm/dd/yy)
[/TD]
[TD]Start time (hh:mm)
[/TD]
[TD]End Date (mm/dd/yy)
[/TD]
[TD]End time (hh:mm)
[/TD]
[TD]Time (in hrs)
[/TD]
[TD]Status
[/TD]
[TD]AHT
[/TD]
[/TR]
[TR]
[TD]Case 1
[/TD]
[TD]DE
[/TD]
[TD]09-24-19
[/TD]
[TD]09:25:00
[/TD]
[TD]09-24-19
[/TD]
[TD]12:52:00
[/TD]
[TD]3:27
[/TD]
[TD]Completed
[/TD]
[TD]03:27
[/TD]
[/TR]
[TR]
[TD]Case 2
[/TD]
[TD]DE
[/TD]
[TD]09-24-19
[/TD]
[TD]12:54:00
[/TD]
[TD]09-24-19
[/TD]
[TD]13:01:00
[/TD]
[TD]0:07
[/TD]
[TD]In Progress
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]DE
[/TD]
[TD]09-24-19
[/TD]
[TD]13:52:00
[/TD]
[TD]09-24-19
[/TD]
[TD]15:39:00
[/TD]
[TD]1:47
[/TD]
[TD]In Progress
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]Meeting
[/TD]
[TD]09-24-19
[/TD]
[TD]15:39:00
[/TD]
[TD]09-24-19
[/TD]
[TD]17:33:00
[/TD]
[TD]1:54
[/TD]
[TD]Completed
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]DE
[/TD]
[TD]09-25-19
[/TD]
[TD]09:12:00
[/TD]
[TD]09-25-19
[/TD]
[TD]10:35:00
[/TD]
[TD]1:23
[/TD]
[TD]Completed
[/TD]
[TD]03:24
[/TD]
[/TR]
[TR]
[TD]Case 3
[/TD]
[TD]DE
[/TD]
[TD]09-25-19
[/TD]
[TD]10:50:00
[/TD]
[TD]09-25-19
[/TD]
[TD]12:48:00
[/TD]
[TD]1:58
[/TD]
[TD]Completed
[/TD]
[TD]01:58
[/TD]
[/TR]
[TR]
[TD]Case 4
[/TD]
[TD]DE
[/TD]
[TD]09-25-19
[/TD]
[TD]12:49:00
[/TD]
[TD]09-25-19
[/TD]
[TD]13:01:00
[/TD]
[TD]0:12
[/TD]
[TD]In Progress
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]DE
[/TD]
[TD]09-25-19
[/TD]
[TD]13:47:00
[/TD]
[TD]09-25-19
[/TD]
[TD]14:59:00
[/TD]
[TD]1:12
[/TD]
[TD]Completed
[/TD]
[TD]01:24
[/TD]
[/TR]
[TR]
[TD]Case 5
[/TD]
[TD]DE
[/TD]
[TD]09-25-19
[/TD]
[TD]15:05:00
[/TD]
[TD]09-25-19
[/TD]
[TD]15:20:00
[/TD]
[TD]0:15
[/TD]
[TD]In Progress
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]Query Resolution
[/TD]
[TD]09-25-19
[/TD]
[TD]15:20:00
[/TD]
[TD]09-25-19
[/TD]
[TD]15:45:00
[/TD]
[TD]0:25
[/TD]
[TD]Completed
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]DE
[/TD]
[TD]09-25-19
[/TD]
[TD]15:46:00
[/TD]
[TD]09-25-19
[/TD]
[TD]16:30:00
[/TD]
[TD]0:44
[/TD]
[TD]In Progress
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]Query Resolution
[/TD]
[TD]09-25-19
[/TD]
[TD]16:31:00
[/TD]
[TD]09-25-19
[/TD]
[TD]16:37:00
[/TD]
[TD]0:06
[/TD]
[TD]Completed
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]DE
[/TD]
[TD]09-25-19
[/TD]
[TD]16:37:00
[/TD]
[TD]09-25-19
[/TD]
[TD]17:28:00
[/TD]
[TD]0:51
[/TD]
[TD]In Progress
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]DE
[/TD]
[TD]09-26-19
[/TD]
[TD]09:13:00
[/TD]
[TD]09-26-19
[/TD]
[TD]09:48:00
[/TD]
[TD]0:35
[/TD]
[TD]In Progress
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]Working on Feedback
[/TD]
[TD]09-26-19
[/TD]
[TD]09:48:00
[/TD]
[TD]09-26-19
[/TD]
[TD]12:23:00
[/TD]
[TD]2:35
[/TD]
[TD]Completed
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]DE
[/TD]
[TD]09-26-19
[/TD]
[TD]12:23:00
[/TD]
[TD]09-26-19
[/TD]
[TD]13:01:00
[/TD]
[TD]0:38
[/TD]
[TD]In Progress
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]DE
[/TD]
[TD]09-26-19
[/TD]
[TD]13:48:00
[/TD]
[TD]09-26-19
[/TD]
[TD]14:24:00
[/TD]
[TD]0:36
[/TD]
[TD]In Progress
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]Meeting
[/TD]
[TD]09-26-19
[/TD]
[TD]14:24:00
[/TD]
[TD]09-26-19
[/TD]
[TD]15:24:00
[/TD]
[TD]1:00
[/TD]
[TD]Completed
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]DE
[/TD]
[TD]09-26-19
[/TD]
[TD]15:24:00
[/TD]
[TD]09-26-19
[/TD]
[TD]16:51:00
[/TD]
[TD]1:27
[/TD]
[TD]Completed
[/TD]
[TD]05:06
[/TD]
[/TR]
[TR]
[TD]Case 6
[/TD]
[TD]DE
[/TD]
[TD]09-26-19
[/TD]
[TD]16:52:00
[/TD]
[TD]09-26-19
[/TD]
[TD]17:23:00
[/TD]
[TD]0:31
[/TD]
[TD]In Progress
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]Other
[/TD]
[TD]09-26-19
[/TD]
[TD]17:23:00
[/TD]
[TD]09-26-19
[/TD]
[TD]17:31:00
[/TD]
[TD]0:08
[/TD]
[TD]Completed
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]DE
[/TD]
[TD]09-27-19
[/TD]
[TD]09:20:00
[/TD]
[TD]09-27-19
[/TD]
[TD]11:54:00
[/TD]
[TD]2:34
[/TD]
[TD]In Progress
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]Query Resolution
[/TD]
[TD]09-27-19
[/TD]
[TD]11:54:00
[/TD]
[TD]09-27-19
[/TD]
[TD]12:35:00
[/TD]
[TD]0:41
[/TD]
[TD]Completed
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]DE
[/TD]
[TD]09-27-19
[/TD]
[TD]12:35:00
[/TD]
[TD]09-27-19
[/TD]
[TD]13:01:00
[/TD]
[TD]0:26
[/TD]
[TD]Completed
[/TD]
[TD]03:31
[/TD]
[/TR]
[TR]
[TD]Case 7
[/TD]
[TD]DE
[/TD]
[TD]09-27-19
[/TD]
[TD]16:57:00
[/TD]
[TD]09-27-19
[/TD]
[TD]17:30:00
[/TD]
[TD]00:33
[/TD]
[TD]In Progress
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]DE
[/TD]
[TD]09-27-19
[/TD]
[TD]13:51:00
[/TD]
[TD]09-27-19
[/TD]
[TD]16:06:00
[/TD]
[TD]2:15
[/TD]
[TD]In Progress
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]DE
[/TD]
[TD]09-30-19
[/TD]
[TD]09:11:00
[/TD]
[TD]09-30-19
[/TD]
[TD]10:28:00
[/TD]
[TD]1:17
[/TD]
[TD]In Progress
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]Query Resolution
[/TD]
[TD]09-30-19
[/TD]
[TD]10:29:00
[/TD]
[TD]09-30-19
[/TD]
[TD]10:49:00
[/TD]
[TD]0:20
[/TD]
[TD]Completed
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]DE
[/TD]
[TD]09-30-19
[/TD]
[TD]10:49:00
[/TD]
[TD]09-30-19
[/TD]
[TD]10:56:00
[/TD]
[TD]0:07
[/TD]
[TD]In Progress
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]Meeting
[/TD]
[TD]09-30-19
[/TD]
[TD]10:56:00
[/TD]
[TD]09-30-19
[/TD]
[TD]11:09:00
[/TD]
[TD]0:13
[/TD]
[TD]Completed
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]DE
[/TD]
[TD]09-30-19
[/TD]
[TD]11:10:00
[/TD]
[TD]09-30-19
[/TD]
[TD]12:13:00
[/TD]
[TD]1:03
[/TD]
[TD]In Progress
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]Other
[/TD]
[TD]09-30-19
[/TD]
[TD]12:13:00
[/TD]
[TD]09-30-19
[/TD]
[TD]12:22:00
[/TD]
[TD]0:09
[/TD]
[TD]Completed
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]DE
[/TD]
[TD]09-30-19
[/TD]
[TD]12:23:00
[/TD]
[TD]09-30-19
[/TD]
[TD]12:57:00
[/TD]
[TD]0:34
[/TD]
[TD]Completed
[/TD]
[TD]05:49
[/TD]
[/TR]
[TR]
[TD]Case 8
[/TD]
[TD]DE
[/TD]
[TD]09-30-19
[/TD]
[TD]13:44:00
[/TD]
[TD]09-30-19
[/TD]
[TD]15:49:00
[/TD]
[TD]2:05
[/TD]
[TD]Completed
[/TD]
[TD]02:05
[/TD]
[/TR]
[TR]
[TD]Case 9
[/TD]
[TD]DE
[/TD]
[TD]09-30-19
[/TD]
[TD]15:50:00
[/TD]
[TD]09-30-19
[/TD]
[TD]17:30:00
[/TD]
[TD]1:40
[/TD]
[TD]In Progress
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]DE
[/TD]
[TD]10-01-19
[/TD]
[TD]09:14:00
[/TD]
[TD]10-01-19
[/TD]
[TD]11:09:00
[/TD]
[TD]1:55
[/TD]
[TD]Completed
[/TD]
[TD]03:35
[/TD]
[/TR]
</tbody>[/TABLE]
 
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
You must log in or register to reply here.

Similar threads

A
  • Solved
Count entry by time
Replies
3
Views
194
admvlad
A
tezza
  • Solved
Autosum specific rows and columns
Replies
2
Views
270
tezza
tezza
B
  • Question Question
Excel Pivot Table to see the number of "Lost Calls"
Replies
0
Views
64
Bernie Galve
B
welshgasman
  • Question Question
Formulae jumping references?
Replies
7
Views
88
welshgasman
welshgasman
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
316
dshtesy
D

Forum statistics

Threads
1,223,888
Messages
6,175,203
Members
452,617
Latest member
Narendra Babu D

Share this page

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