Turn Around Time (Above average Excel users)

[rplim2016]

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.

Case Number <tbody> </tbody>	Open <tbody> </tbody>	Edit Date <tbody> </tbody>	Old Value <tbody> </tbody>	New Value <tbody> </tbody>
11543228 <tbody> </tbody>	08/01/2018 0:01 <tbody> </tbody>	08/01/2018 0:23 <tbody> </tbody>	Open <tbody> </tbody>	In Progress <tbody> </tbody>
11543228 <tbody> </tbody>	08/01/2018 0:01 <tbody> </tbody>	08/01/2018 0:25 <tbody> </tbody>	In Progress <tbody> </tbody>	Pending Internal <tbody> </tbody>
11543228 <tbody> </tbody>	08/01/2018 0:01 <tbody> </tbody>	08/02/2018 13:56 <tbody> </tbody>	Pending Internal <tbody> </tbody>	In Progress <tbody> </tbody>
11543228 <tbody> </tbody>	08/01/2018 0:01 <tbody> </tbody>	08/02/2018 14:36 <tbody> </tbody>	In Progress <tbody> </tbody>	Pending Internal <tbody> </tbody>
11543228 <tbody> </tbody>	08/01/2018 0:01 <tbody> </tbody>	08/20/2018 13:25 <tbody> </tbody>	Pending Internal <tbody> </tbody>	In Progress <tbody> </tbody>
11543228 <tbody> </tbody>	08/01/2018 0:01 <tbody> </tbody>	08/20/2018 14:38 <tbody> </tbody>	In Progress <tbody> </tbody>	Resolved <tbody> </tbody>
11543228 <tbody> </tbody>	08/01/2018 0:01 <tbody> </tbody>	08/23/2018 15:17 <tbody> </tbody>	Resolved <tbody> </tbody>	Closed <tbody> </tbody>

<tbody>
</tbody>

 

[rplim2016]

up.

 

[steve the fish]

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??

 

[Kyle123]

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?

 

[Kyle123]

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