Totalling time quantities

[dave knightly]

I download my bt e.bill into Excel. One column lists actual time per
call in the following format

0000:00:00 where for example a call lasting 1hour 16 minutes and 20
seconds would display as 0001:16:20

How can I sum the list of figures to produce a total of calls
duration? I have tried formatting each time cell as hhhh:mm:ss but
this does not work.

 

[Gary Brown]

Create a formula that references the text column showing
the 0001:16:20.
The formula (assume in cell 'A1') would be something
like...
=((VALUE(LEFT(A1,4))*60)+(VALUE(MID(A1,6,2)))+(VALUE(RIGHT
(A1,2))/60))/(60*24)
Format these cells [and the total] as...
dd:hh:mm:ss
This will show Days:Hours:Minutes:Seconds.
Hope this helps.
Gary Brown

 

[dave knightly]

Create a formula that references the text column showing
the 0001:16:20.
The formula (assume in cell 'A1') would be something
like...
=((VALUE(LEFT(A1,4))*60)+(VALUE(MID(A1,6,2)))+(VALUE(RIGHT
(A1,2))/60))/(60*24)
Format these cells [and the total] as...
dd:hh:mm:ss
This will show Days:Hours:Minutes:Seconds.
Hope this helps.
Gary Brown

-----Original Message-----
I download my bt e.bill into Excel. One column lists actual time per
call in the following format

0000:00:00 where for example a call lasting 1hour 16 minutes and 20
seconds would display as 0001:16:20

How can I sum the list of figures to produce a total of calls
duration? I have tried formatting each time cell as hhhh:mm:ss but
this does not work.
.

Gary

You are a genius. Thank you very much.


Regards

Dave