summing data in Open Office Calc

Question

I have a huge data containing time in the following format

0055:24
0020:24
0034:24
0025:24

There are 50,000 records that need to be added up all these time.

Is there a quick macro for Open Office Calc or suggest some easy way to do it?

I have tried in the following ways without success:

1. Copied all the data and pasted in the Calc
2. After pasting the time, time automatically gets changed according to the Calc standard format.
3. I cannot add up to the sum

Answer 1

In the paste step you should see a Text Import dialog box. In the Separated by section, check the Other box, then put a colon : in the text entry field to the right, then click OK. This will import your data in two columns of numbers. Presumably these are hours and minutes or minutes and seconds.

Generate a third column that is the sum of the first column and the second column divided by 60, that is =C1+C2/60. The new column is hours or minutes and you can sum that column to get the total hours or minutes. I'm guessing that is what you want. If not, please clarify your time format and what you are trying to get out of the sum.