Summing times in a pivot report

Mel C Roberts

New Member
Joined
Feb 26, 2002
Messages
40
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?
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
Book1
ABCDEFGHIJ
1StartFinishElapsedAverageofElapsedTotal
29/14/0212:209/16/0219:0254:42:13Total241:22:00
39/16/020:289/22/023:22146:54:03
49/17/0217:019/27/0218:33241:32:30
59/19/020:2310/2/0219:28331:05:44
69/20/0210:1710/8/0210:52432:35:28
7
Sheet2
 

Watch MrExcel Video

Forum statistics

Threads
1,118,436
Messages
5,572,090
Members
412,441
Latest member
kelethymos
Top