Adding minutes and seconds in Excel

Joechick

New Member
Joined
Apr 23, 2002
Messages
1
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?
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top