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.
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
<tbody>
</tbody>
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.
A | B | C | D | E | F | G | H | I | J | K | L | M | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | id | Status | Organization | Request date | Solved date | Latest update | |||||||
2 | 2100 | Open | Client 1 | 3/8/2018 11:42 | 3/8/2018 11:50 | Average hours to solve | ######## | Client | Client 1 | ||||
3 | 2098 | Open | Client 1 | 3/8/2018 9:08 | 3/8/2018 9:12 | sum Open | 43167.49 | ||||||
4 | 2082 | Closed | Client 1 | 3/6/2018 10:29 | 3/6/2018 10:31 | 3/6/2018 10:31 | sum Solve | 0 | |||||
5 | 2076 | On-hold | Client 1 | 3/5/2018 13:58 | 3/6/2018 8:38 | ||||||||
6 | 2072 | Solved | Client 1 | 3/5/2018 11:18 | 3/5/2018 11:35 | 3/5/2018 11:35 | |||||||
7 | 2069 | Closed | Client 1 | 3/2/2018 10:35 | 3/6/2018 8:38 | 3/6/2018 8:38 | |||||||
8 | 2068 | Solved | Client 1 | 3/2/2018 10:14 | 3/8/2018 9:19 | 3/8/2018 9:19 | |||||||
9 | 2060 | Open | Client 1 | 3/1/2018 10:03 | 3/1/2018 10:53 | ||||||||
10 | 2053 | Pending | Client 1 | 2/28/2018 10:13 | 3/6/2018 7:02 | ||||||||
11 | 2050 | Closed | Client 1 | 2/27/2018 12:06 | 2/27/2018 12:39 | 3/3/2018 13:06 | |||||||
12 | 2044 | On-hold | Client 1 | 2/26/2018 17:33 | 2/27/2018 14:25 | ||||||||
13 | 2043 | Closed | Client 1 | 2/26/2018 17:20 | 3/6/2018 8:38 | 3/6/2018 8:38 | |||||||
14 | 2041 | Pending | Client 1 | 2/26/2018 13:58 | 3/5/2018 10:28 | ||||||||
15 | 2038 | On-hold | Client 1 | 2/26/2018 10:27 | 3/8/2018 12:08 | ||||||||
16 | 2037 | Closed | Client 1 | 2/23/2018 14:44 | 2/27/2018 12:53 | 3/3/2018 13:06 | |||||||
17 | 2021 | Closed | Client 1 | 2/22/2018 7:29 | 2/23/2018 8:35 | 2/27/2018 9:05 | |||||||
18 | 2019 | Closed | Client 1 | 2/20/2018 17:08 | 2/27/2018 12:53 | 3/3/2018 13:06 | |||||||
19 | 2018 | Closed | Client 1 | 2/20/2018 16:45 | 2/27/2018 10:51 | 3/3/2018 11:07 | |||||||
20 | 2007 | Pending | Client 1 | 2/16/2018 13:06 | 3/2/2018 13:29 | ||||||||
21 | 1984 | Closed | Client 1 | 2/15/2018 10:06 | 2/15/2018 10:09 | 2/15/2018 10:09 | |||||||
22 | 1983 | Closed | Client 1 | 2/15/2018 9:51 | 2/15/2018 11:13 | 2/19/2018 12:10 | |||||||
23 | 1982 | Closed | Client 1 | 2/15/2018 9:20 | 2/15/2018 10:20 | 2/19/2018 11:04 | |||||||
24 | 1634 | Closed | Client 2 | 12/21/2017 13:37 | 12/21/2017 14:46 | 12/25/2017 15:06 | |||||||
25 | 2091 | Solved | Client 2 | 3/7/2018 8:36 | 3/7/2018 8:56 | 3/7/2018 8:56 | |||||||
26 | 2009 | Closed | Client 2 | 2/20/2018 5:33 | 2/21/2018 5:56 | 2/25/2018 6:03 | |||||||
27 | 1995 | Closed | Client 2 | 2/16/2018 6:19 | 2/16/2018 9:07 | 2/20/2018 10:02 | |||||||
28 | 1951 | Closed | Client 2 | 2/13/2018 9:51 | 2/13/2018 10:10 | 2/17/2018 11:07 | |||||||
29 | 1946 | Closed | Client 2 | 2/12/2018 13:29 | 2/12/2018 14:33 | 2/16/2018 15:08 | |||||||
30 | 1869 | Closed | Client 2 | 1/30/2018 12:06 | 2/16/2018 14:23 | 2/20/2018 15:10 | |||||||
31 | 1855 | Closed | Client 2 | 1/29/2018 9:41 | 1/30/2018 12:21 | 2/3/2018 13:05 | |||||||
32 | 1845 | Closed | Client 2 | 1/25/2018 16:35 | 2/7/2018 13:11 | 2/11/2018 14:04 | |||||||
33 | 1835 | Closed | Client 2 | 1/24/2018 12:27 | 1/25/2018 8:30 | 1/29/2018 9:06 | |||||||
34 | 1693 | Closed | Client 2 | 1/2/2018 8:48 | 1/2/2018 9:04 | 1/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
<thead> </thead><tbody> </tbody> |
<tbody>
</tbody>