Sum minutes and seconds to total hours

[Guest]

I import a text file into Excel. I need to add one column of data that
contains minutes and seconds. I can use the SUM function to add a range
okay, however, when the total is more than one hour it fails to show me the
hour column and displays a small value in minutes only. I need to be able to
add the minutes and seconds and come up with a total showing the total hours
minutes and seconds for a range in this column. I listed the exact format
that I import into Excel below. Thank you in advance.

0:15
3:00
0:30
0:15

 

[Dave Peterson]

First, if you select one of those cells and look at the formula bar, I bet you
see:

03:00:00 (with 03:00 selected).

This means that excel is seeing your data as hh:mm:ss--not just mm:ss.

You can do the arithmetic in the cell with the sum:

=sum(a1:a4)/60
(format as mm:ss or [mm]:ss if you go past 60 minutes)

or you could fix your data.
put 60 in an empty cell
edit|copy that cell
select your range of times
edit|paste special|check divide
clean up that helper cell
and format the original range the way you want: mm:ss

Then the cell with the sum would show just:
=SUM(A1:A4)
(still format the way you want: [mm]:ss)

 

[Earl Kiosterud]

Deck,

I'm going to have to take a guess here on what you're describing. Does this
added column have a formula that sums the rows of times??? If so, it may
format itself to show hh:mm, which might look like mm:ss. Try changing the
format (Format - Cells - Number - Custom) to hh:mm:ss.

 

[Guest]

the /60 added at the end of my formula did the trick. Thanks again!

First, if you select one of those cells and look at the formula bar, I bet you
see:

03:00:00 (with 03:00 selected).

This means that excel is seeing your data as hh:mm:ss--not just mm:ss.

You can do the arithmetic in the cell with the sum:

=sum(a1:a4)/60
(format as mm:ss or [mm]:ss if you go past 60 minutes)

or you could fix your data.
put 60 in an empty cell
edit|copy that cell
select your range of times
edit|paste special|check divide
clean up that helper cell
and format the original range the way you want: mm:ss

Then the cell with the sum would show just:
=SUM(A1:A4)
(still format the way you want: [mm]:ss)

I import a text file into Excel. I need to add one column of data that
contains minutes and seconds. I can use the SUM function to add a range
okay, however, when the total is more than one hour it fails to show me the
hour column and displays a small value in minutes only. I need to be able to
add the minutes and seconds and come up with a total showing the total hours
minutes and seconds for a range in this column. I listed the exact format
that I import into Excel below. Thank you in advance.

0:15
3:00
0:30
0:15