Find all dates related to a client and then find the average time difference between them

corruptedlogic

New Member
Joined
Mar 8, 2018
Messages
12
Hi there, I have an export from our zendesk helpdesk and am trying to find the average time to solve a ticket. So far, I have tried to sum the Open date and the solved dates, then average the result. that seems to get me the correct answer ( i think!) until the first "solved date" is blank (because the first row for a client is something other than a solved or closed ticket), in that scenario i just get a whole bunch of ###### characters. I'm hoping someone can point me in the right direction to either only iterate through solved or closed tickets or somehow ignore the blanks in the solved date column. Totally open to suggestions!

The formulae i currently have are:

Sum solved dates: =INDEX(E:E,MATCH(M2,C:C,0))
Sum Open Dates: =INDEX(D:D,MATCH(M2,C:C,0))
Calc average solve time: =(I4-I3)*24

Thanks to all in advance, sample data set attached.
ABCDEFGHIJKLM
1idStatusOrganizationRequest dateSolved dateLatest update
22100OpenClient 13/8/2018 11:423/8/2018 11:50Average hours to solve########ClientClient 1
32098OpenClient 13/8/2018 9:083/8/2018 9:12sum Open43167.49
42082ClosedClient 13/6/2018 10:293/6/2018 10:313/6/2018 10:31sum Solve0
52076On-holdClient 13/5/2018 13:583/6/2018 8:38
62072SolvedClient 13/5/2018 11:183/5/2018 11:353/5/2018 11:35
72069ClosedClient 13/2/2018 10:353/6/2018 8:383/6/2018 8:38
82068SolvedClient 13/2/2018 10:143/8/2018 9:193/8/2018 9:19
92060OpenClient 13/1/2018 10:033/1/2018 10:53
102053PendingClient 12/28/2018 10:133/6/2018 7:02
112050ClosedClient 12/27/2018 12:062/27/2018 12:393/3/2018 13:06
122044On-holdClient 12/26/2018 17:332/27/2018 14:25
132043ClosedClient 12/26/2018 17:203/6/2018 8:383/6/2018 8:38
142041PendingClient 12/26/2018 13:583/5/2018 10:28
152038On-holdClient 12/26/2018 10:273/8/2018 12:08
162037ClosedClient 12/23/2018 14:442/27/2018 12:533/3/2018 13:06
172021ClosedClient 12/22/2018 7:292/23/2018 8:352/27/2018 9:05
182019ClosedClient 12/20/2018 17:082/27/2018 12:533/3/2018 13:06
192018ClosedClient 12/20/2018 16:452/27/2018 10:513/3/2018 11:07
202007PendingClient 12/16/2018 13:063/2/2018 13:29
211984ClosedClient 12/15/2018 10:062/15/2018 10:092/15/2018 10:09
221983ClosedClient 12/15/2018 9:512/15/2018 11:132/19/2018 12:10
231982ClosedClient 12/15/2018 9:202/15/2018 10:202/19/2018 11:04
241634ClosedClient 212/21/2017 13:3712/21/2017 14:4612/25/2017 15:06
252091SolvedClient 23/7/2018 8:363/7/2018 8:563/7/2018 8:56
262009ClosedClient 22/20/2018 5:332/21/2018 5:562/25/2018 6:03
271995ClosedClient 22/16/2018 6:192/16/2018 9:072/20/2018 10:02
281951ClosedClient 22/13/2018 9:512/13/2018 10:102/17/2018 11:07
291946ClosedClient 22/12/2018 13:292/12/2018 14:332/16/2018 15:08
301869ClosedClient 21/30/2018 12:062/16/2018 14:232/20/2018 15:10
311855ClosedClient 21/29/2018 9:411/30/2018 12:212/3/2018 13:05
321845ClosedClient 21/25/2018 16:352/7/2018 13:112/11/2018 14:04
331835ClosedClient 21/24/2018 12:271/25/2018 8:301/29/2018 9:06
341693ClosedClient 21/2/2018 8:481/2/2018 9:041/6/2018 10:07

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Zendesk Data

Worksheet Formulas
CellFormula
I2=(I4-I3)*24
I3=INDEX(D:D,MATCH(M2,C:C,0))
I4=INDEX(E:E,MATCH(M2,C:C,0))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Your example only looks up the first client/date. Also, instead of averaging the sums you should have a column of differences and conditionally average them:


Excel 2010
ABCDEFGHIJKLMN
1idStatusOrganizationRequest dateSolved dateDiffLatest update
22100OpenClient 13/8/2018 11:42 3/8/2018 11:50Average hours to solve66.83205128ClientClient 1
32098OpenClient 13/8/2018 9:083/8/2018 9:12sum Open
42082ClosedClient 13/6/2018 10:293/6/2018 10:310.0333333333/6/2018 10:31sum Solve
52076On-holdClient 13/5/2018 13:583/6/2018 8:38
62072SolvedClient 13/5/2018 11:183/5/2018 11:350.2833333333/5/2018 11:35
72069ClosedClient 13/2/2018 10:353/6/2018 8:3894.053/6/2018 8:38
82068SolvedClient 13/2/2018 10:143/8/2018 9:19143.08333333/8/2018 9:19
92060OpenClient 13/1/2018 10:033/1/2018 10:53
102053PendingClient 12/28/2018 10:133/6/2018 7:02
112050ClosedClient 12/27/2018 12:062/27/2018 12:390.553/3/2018 13:06
122044On-holdClient 12/26/2018 17:332/27/2018 14:25
132043ClosedClient 12/26/2018 17:203/6/2018 8:38183.33/6/2018 8:38
142041PendingClient 12/26/2018 13:583/5/2018 10:28
152038On-holdClient 12/26/2018 10:273/8/2018 12:08
162037ClosedClient 12/23/2018 14:442/27/2018 12:5394.153/3/2018 13:06
172021ClosedClient 12/22/2018 7:292/23/2018 8:3525.12/27/2018 9:05
182019ClosedClient 12/20/2018 17:082/27/2018 12:53163.753/3/2018 13:06
192018ClosedClient 12/20/2018 16:452/27/2018 10:51162.13/3/2018 11:07
202007PendingClient 12/16/2018 13:063/2/2018 13:29
211984ClosedClient 12/15/2018 10:062/15/2018 10:090.052/15/2018 10:09
221983ClosedClient 12/15/2018 9:512/15/2018 11:131.3666666672/19/2018 12:10
231982ClosedClient 12/15/2018 9:202/15/2018 10:2012/19/2018 11:04
241634ClosedClient 212/21/2017 13:3712/21/2017 14:461.1512/25/2017 15:06
252091SolvedClient 23/7/2018 8:363/7/2018 8:560.3333333333/7/2018 8:56
262009ClosedClient 22/20/2018 5:332/21/2018 5:5624.383333332/25/2018 6:03
271995ClosedClient 22/16/2018 6:192/16/2018 9:072.82/20/2018 10:02
281951ClosedClient 22/13/2018 9:512/13/2018 10:100.3166666672/17/2018 11:07
291946ClosedClient 22/12/2018 13:292/12/2018 14:331.0666666672/16/2018 15:08
301869ClosedClient 21/30/2018 12:062/16/2018 14:23410.28333332/20/2018 15:10
311855ClosedClient 21/29/2018 9:411/30/2018 12:2126.666666672/3/2018 13:05
321845ClosedClient 21/25/2018 16:352/7/2018 13:11308.62/11/2018 14:04
331835ClosedClient 21/24/2018 12:271/25/2018 8:3020.051/29/2018 9:06
341693ClosedClient 21/2/2018 8:481/2/2018 9:040.2666666671/6/2018 10:07
Sheet6
Cell Formulas
RangeFormula
F2=IF(E2=0,"",24*(E2-D2))
J2=AVERAGEIF($C$2:$C$34,N2,$F$2:$F$34)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

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