Calculating time taken

[silentbob30]

Hello,

I am putting together a spreadsheet to report on how long it takes someone to close a ticket from the time it is opened.

in column A I have a list of ticket numbers, in F I have whether the call was created or closed. The format of these is CALL CREATED and CLOSED. In column GI have a date and time in the format of 05/11/2010 15:49.

The data is column A is in no order so call 1 could be opened on row 2 but not closed until row 10 and in the middle several other tickets could of been opened and closed. -- Hope that makes sense

If I have a list of all the ticket numbers in sheet 2 column A is there a formula I can use to work out the length of time taken from the call creation to when it was closed in days, hours and minutes.

THanks for all help offered

 

[GlennUK]

You could do it with a pivottable. Sample source data:

Excel Workbook
	A	B	C	D	E	F	G
1	Ticket id	Blah1	Blah2	Blah3	Blah4	Status	Stamp
2	T123AA	sfsddf	sfsddf	sfsddf	sfsddf	CALL CREATED	10/01/2011 13:32
3	T2FFF	sfsddf	sfsddf	sfsddf	sfsddf	CALL CREATED	12/02/2011 09:45
4	T2FFF	sfsddf	sfsddf	sfsddf	sfsddf	CLOSED	14/02/2011 08:32
5	T3HGT	sfsddf	sfsddf	sfsddf	sfsddf	CALL CREATED	16/02/2011 16:34
6	T4LKI	sfsddf	sfsddf	sfsddf	sfsddf	CALL CREATED	18/02/2011 11:11
7	T123AA	sfsddf	sfsddf	sfsddf	sfsddf	CLOSED	21/02/2011 12:45
8	T5DDE	sfsddf	sfsddf	sfsddf	sfsddf	CALL CREATED	22/02/2011 14:33
9	T4LKI	sfsddf	sfsddf	sfsddf	sfsddf	CLOSED	23/02/2011 17:32
10	T3HGT	sfsddf	sfsddf	sfsddf	sfsddf	CLOSED	24/02/2011 12:12
11	T5DDE	sfsddf	sfsddf	sfsddf	sfsddf	CLOSED	25/02/2011 13:14
Sheet1

Example PivotTable:

Excel Workbook
	H	I	J	K	L	M
1		Sum of Stamp	Status
2		Ticket id	CALL CREATED	CLOSED	Formula1
3		T123AA	10/01/2011 13:32	21/02/2011 12:45	10 days 23:13
4		T2FFF	12/02/2011 09:45	14/02/2011 08:32	1 days 22:47
5		T3HGT	16/02/2011 16:34	24/02/2011 12:12	7 days 19:38
6		T4LKI	18/02/2011 11:11	23/02/2011 17:32	5 days 06:21
7		T5DDE	22/02/2011 14:33	25/02/2011 13:14	2 days 22:41
8
Sheet1

Using Sum for the data items, and a calculated Item to get the difference between CLOSED and CALL CREATED. Custom format of the calculated item gives days and hh:mm ... but this is limited to a max of 31 days. You could still use a pivottable to align the CLOSED and CALL CREATED times and do a calculation for the difference outside of the PivotTable, of course.

 

[silentbob30]

Thanks for that and I can see how it would work I am just having trouble implementing it.

It seems beyond my skill level. Any extra details would be appreciated.

I am not sure what you mean by 'Using Sum for the data items'

 

[silentbob30]

Hi

I am getting somewhere but when I add the details field the pivot table shows the call created and closed fields as blank.

 

[GlennUK]

It's a bit hard to imagine what's going on without some data, and an idea of what your pivottable looks like. Can you post something?