Please help with excel...

[AB]

07884 1xx408 01 Oct 09 19:08 CALL To a mobile 0h 00m 38s £0.15
07852 9xx315 01 Oct 09 19:07 CALL To a mobile 0h 00m 47s £0.17
07852 9xx315 01 Oct 09 19:06 CALL To a mobile 0h 00m 02s £0.10
07900 9xx537 01 Oct 09 19:05 CALL To a mobile 0h 01m 09s £0.20

See above. I have 37,000 entries like this from a customers monthly bill.

I need to add up the duration, but look at the damn format.

I'm not an Excel person so I need help. How can I total the duration? Autosum returns "0".

[aclivity]

First use Text To Columns to break up the list into it's component parts. Make sure the Hour, Minute and second are all separate columns.

Add up the hours, minutes, and seconds.

Divide the total seconds by 60 ... use the whole number part only (e.g. 126 seconds will be 2.1, use the 2).

Add the whole number to the minutes column total.

multiply the whole number above by 60, subtract the product from the total seconds, put that into a "final answer" cell for seconds.

Divide the minutes by 60 ... use the whole number part only (e.g. 126 minutes will be 2.1 or something, use the 2).

Multiply the whole number by 60, subtract it from the total number of minutes.

I think you can get the idea from that.

[Crusoe]

If you use the data/text to columns menu option with the column highlighted you will be able to divide up that line of text so you can have a column with just the hours one with just the minutes and one for the seconds. You then multiply the hours by 3600 and the minutes by 60. Add up all the results and you have the total in seconds.

[AB]

IL_JDM said:

Please don't tell me you guys bill from spreadsheets AB

Thanks chaps, very helpful.

No, we don't bill from spreadsheets, it's an existing customer who is moving his other business over to us now it is out of contract. Would you believe the spreadsheet is from BT...