Turn Around Time (Above average Excel users)

R

rplim2016

Board Regular
Joined
Jun 28, 2016
Messages
76
Hi Mr. Excel Users,

I need to compute the turn around time below.

Note: There is no way to change the way that they extract the data from server.

Condition:
1. There are hundred thousands of duplicate "Case Number", perhaps thousands of unique Cases.
2. I need to calculate the turn around time (TAT) of a unique "Case Number"
3. There is no option to extract or store a "Case Number" in a single row, so the system adds a new row to record the "New Value" History.

What I want to achieve is this:

I wanted to remove the "Case Number" duplicates, then in every single row there will be the TAT.

1. Network days in Header: Open (Date) Edit Date (Date) Header: "Old Value: "In progress" Header: "Resolved".
This is to get the TAT from Open up to Resolved.

Sample: =NETWORKDAYS(B2,C7)

2. I need to calculate the rendered pending time from below, in summary I need to get the dates below with certain condition but the thing is, I'm having difficulties as I think there is no way to solve this multiple row issues.

Please help me. Much appreciated.

PS. Does anyone can give me an idea on how to collate the "Edit Date" & "Old Value" & "New Value" in a single row? Therefore, it would be easier to calculate the TAT in that way. I'm dealing with hundred of thousands of duplicate cases.



[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD="class: xl65, width: 93"]Case Number[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 132"]
<tbody>[TR]
[TD="class: xl65, width: 132"]Open[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 113"]
<tbody>[TR]
[TD="class: xl65, width: 113"]Edit Date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 126"]
<tbody>[TR]
[TD="class: xl65, width: 126"]Old Value[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 126"]
<tbody>[TR]
[TD="class: xl65, width: 126"]New Value[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD="class: xl65, width: 93"]11543228[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 132"]
<tbody>[TR]
[TD="class: xl65, width: 132"]08/01/2018 0:01[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 113"]
<tbody>[TR]
[TD="class: xl65, width: 113"]08/01/2018 0:23[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 126"]
<tbody>[TR]
[TD="class: xl65, width: 126"]Open[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 126"]
<tbody>[TR]
[TD="class: xl65, width: 126"]In Progress[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD="class: xl65, width: 93"]11543228[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 132"]
<tbody>[TR]
[TD="class: xl65, width: 132"]08/01/2018 0:01[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 113"]
<tbody>[TR]
[TD="class: xl65, width: 113"]08/01/2018 0:25[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 126"]
<tbody>[TR]
[TD="class: xl65, width: 126"]In Progress[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 126"]
<tbody>[TR]
[TD="class: xl65, width: 126"]Pending Internal[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD="class: xl65, width: 93"]11543228[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 132"]
<tbody>[TR]
[TD="class: xl65, width: 132"]08/01/2018 0:01[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 113"]
<tbody>[TR]
[TD="class: xl65, width: 113"]08/02/2018 13:56[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 126"]
<tbody>[TR]
[TD="class: xl65, width: 126"]Pending Internal[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 126"]
<tbody>[TR]
[TD="class: xl65, width: 126"]In Progress[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD="class: xl65, width: 93"]11543228[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 132"]
<tbody>[TR]
[TD="class: xl65, width: 132"]08/01/2018 0:01[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 113"]
<tbody>[TR]
[TD="class: xl65, width: 113"]08/02/2018 14:36[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 126"]
<tbody>[TR]
[TD="class: xl65, width: 126"]In Progress[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 126"]
<tbody>[TR]
[TD="class: xl65, width: 126"]Pending Internal[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD="class: xl65, width: 93"]11543228[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 132"]
<tbody>[TR]
[TD="class: xl65, width: 132"]08/01/2018 0:01[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 113"]
<tbody>[TR]
[TD="class: xl65, width: 113"]08/20/2018 13:25[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 126"]
<tbody>[TR]
[TD="class: xl65, width: 126"]Pending Internal[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 126"]
<tbody>[TR]
[TD="class: xl65, width: 126"]In Progress[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD="class: xl65, width: 93"]11543228[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 132"]
<tbody>[TR]
[TD="class: xl65, width: 132"]08/01/2018 0:01[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 113"]
<tbody>[TR]
[TD="class: xl65, width: 113"]08/20/2018 14:38[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 126"]
<tbody>[TR]
[TD="class: xl65, width: 126"]In Progress[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 126"]
<tbody>[TR]
[TD="class: xl65, width: 126"]Resolved[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD="class: xl65, width: 93"]11543228[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 132"]
<tbody>[TR]
[TD="class: xl65, width: 132"]08/01/2018 0:01[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 113"]
<tbody>[TR]
[TD="class: xl65, width: 113"]08/23/2018 15:17[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 126"]
<tbody>[TR]
[TD="class: xl65, width: 126"]Resolved[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 126"]
<tbody>[TR]
[TD="class: xl65, width: 126"]Closed[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
Have you read your post back? Ive read it twice and have no clue as to what you ask for. The time between open and closed??
 
Upvote 0
Are you just wanting the difference between the max and min times in Edit date for each case number, or the difference between the max Edit date and the Open date for each case number?
 
Upvote 0
Why don't you just do this with a Pivot Table? Stick a formula on the end of your data that does the diff between open and edit date (=IF(E2="Resolved",NETWORKDAYS(B2,C2),0)), pivot it with the Case number in the rows and the max of the calculated column in the values
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Duration (Time Difference) between 2 date fields in different rows
Replies
3
Views
619
Leo Skywalker
L
P
  • Solved
Select Case in VBA
Replies
3
Views
670
rollis13
rollis13
C
  • Question Question
ole db CopyFromRecordset skipping cell values
Replies
0
Views
652
Clif McIrvin
C
E
  • Question Question
FIFO Macro giving different results each time it runs
Replies
7
Views
1K
Lee Wei Sin
L
S
  • Solved
VBA copy template sheet to new monthly workbook with daily sheets tabname and header A2:J2 named as "dd mm yyyy" + autosave filename predefined
Replies
4
Views
796
mumps
mumps

Forum statistics

Threads
1,223,908
Messages
6,175,307
Members
452,633
Latest member
DougMo

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