Adding minutes and seconds in Excel

[Joechick]

I have a column of data in the form of minutes and seconds:
ex: 424:06.0 (424 minutes and 6.0 seconds).

I would like to add the rows to give me a total number of minutes and seconds.

Whenever I choose the regular sum function the calculation will not work. I believe that Excel is seeing the data as time on a 24 hour clock and not minutes and seconds.

Can you help?

[Iain Lewis]

Not sure how to do this just as a sum of a column, but you could try this. If you have the time info in A1 to A10 then put :

=TRIM(MID(B1,1,SEARCH(":",B1,1)-1))

in B1 & copy down. Put :

=TRIM(MID(B1,SEARCH(":",B1,1)+1,80))

In C1 & copy down. This will split the minutes from the hours. To show a Total in A12 put the following formula :

=SUM(B1:B10)&":"&SUM(C1:C10)

Hopefully this will give you what you want.

[Iain Lewis]

Slight update need. The formula should be

=TRIM(MID(B1,1,SEARCH(":",B1,1)-1))+0

and

=TRIM(MID(B1,SEARCH(":",B1,1)+1,80))+0

so that Excel thinks they are numbers.

[Mark W.]

On 2002-04-24 05:58, Joechick wrote:
I have a column of data in the form of minutes and seconds:
ex: 424:06.0 (424 minutes and 6.0 seconds).

I would like to add the rows to give me a total number of minutes and seconds.

Whenever I choose the regular sum function the calculation will not work. I believe that Excel is seeing the data as time on a 24 hour clock and not minutes and seconds.

Can you help?

Format the cell containing SUM as...

[m]:ss.0


If your time values are entered as text (in cells A1:A5, for example) you could sum them using...

{=SUM(("0:"&A1:A5)+0)}

...and then apply the same formatting as shown above.

Note: This is an array formula which is entered using the Control+Shfit+Enter key combination. For more on array formulas see the Excel Help Index topic for "About array formulas and how to enter them".

[ This Message was edited by: Mark W. on 2002-04-24 12:00 ]