Sum minutes and seconds to total hours

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

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)



deck4 wrote:
>
> 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

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.
--
Earl Kiosterud
www.smokeylake.

"deck4" <[email protected]> wrote in message
news:[email protected]...
>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
>

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


"Dave Peterson" wrote:

> 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)
>
>
>
> deck4 wrote:
> >
> > 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
>