Summing times in a pivot report

[Mel C Roberts]

Please help.

I have been getting some very funny results from time calculations. After further investigation i have discovered that as soon as the total time gets to 24 hours it reverts to the 24 hour clock and when you then add in another 5 minutes of time the sum total reads 00:05 not 24:05. Then when i am trying to get an average time it is dividing 00:05 by the number of incidents and giving a really small average time.

I have approximately 1300 items in by spreadsheet. And i am trying to calculate an average time for various scenarios using a pivot table.

The raw data is as follows
1) Start time in the format 12-Apr-2002 17:08:00
2) Finish time in the format 12-Apr-2002 17:38:00
3) Total time 1) - 2) = 30:00

Can anyone suggest a way of making sure that when i sum the 1300 items i am getting a running total and not some version of the 24 hour clock - without me having to redo every piece of information?

 

[Mudface]

Try number formatting your sum item as [hh]:mm:ss.

 

[Mark W.]

Book1
	A	B	C	D	E	F	G	H	I	J
1	Start	Finish	Elapsed		AverageofElapsed	Total
2	9/14/0212:20	9/16/0219:02	54:42:13		Total	241:22:00
3	9/16/020:28	9/22/023:22	146:54:03
4	9/17/0217:01	9/27/0218:33	241:32:30
5	9/19/020:23	10/2/0219:28	331:05:44
6	9/20/0210:17	10/8/0210:52	432:35:28
7
Sheet2

 

[Mel C Roberts]

Thanks very much for all your help.
Mission solved.