Adding minutes showing total in hours/minutes, i.e., 60 mins + 60 mins + 15 mins to total of 2 hours 15 mins?

I've been fiddling and fiddling for some time now this evening but I
seem to be missing something completely today. In the past I've dealt
with adding/subtracting times formatted the same way as the end
results, so they've worked from the get-go.

In today's case, I have a spreadsheet that I'm using to calculate best
fits for movies transferred from VHS to DVD via a DVD Recorder. My
DVD Recorder deals with hours and minutes but my internet source gives
total running time in minutes only.

How do I get Excel 2000 to add up a column of minutes and then give
the results in a time formatted as hours and minutes? If I could get
this to work, I could then just plug in different movie running times
finding best fits?

Thanks! I'm in the corner with my dunce cap, I can't figure out my
approach today in a way that works. :oD

Take your total minutes, divide by 1440, and format as hh:mm

--
Regards,
Fred


"StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
news:u777h11h0ncqbhvp83284985erd18204ub@4ax.com...
> I've been fiddling and fiddling for some time now this evening but I
> seem to be missing something completely today. In the past I've dealt
> with adding/subtracting times formatted the same way as the end
> results, so they've worked from the get-go.
>
> In today's case, I have a spreadsheet that I'm using to calculate best
> fits for movies transferred from VHS to DVD via a DVD Recorder. My
> DVD Recorder deals with hours and minutes but my internet source gives
> total running time in minutes only.
>
> How do I get Excel 2000 to add up a column of minutes and then give
> the results in a time formatted as hours and minutes? If I could get
> this to work, I could then just plug in different movie running times
> finding best fits?
>
> Thanks! I'm in the corner with my dunce cap, I can't figure out my
> approach today in a way that works. :oD
>

SGFan,

If you want minutes and hours to take care of themselves, you must use bona
fide time values, formatted (Format - Cells - Number) appropriately. Plain
numbers ( like 45 for 45 minutes ) will require you to use your own formulas
to figure out hours and minutes.

If you type in the format xx:yy, it will be interpreted by Excel as hours
and minutes. To enter minutes only, you must enter it as 0:m or 0:mm. Now
you have a true time value, and you need only sum the cells with an ordinary
formula, like =SUM(A2:A20), and ensure the formatting is what you need
(Format - Cells - Custom - hh:mm).

If the internet source is in plain numbers representing minutes (like 45 for
00:45), it will have to be converted to a number, as it's probably being
brought in as text, and Excel is **** about data typing -- text vs. numbers,
and will have to be converted to a time-serial (divided by 24*60), before
normal time formatting will work. You could do this with each time value
(before they're summed), or sum the plain numbers, dividing the sum by 24*60
and applying the formatting as before). =SUM(A2:A20)/(24*60). You may
have to use =SUM(--A2:A20)/(24*60) entered as an arry formula
(Ctrl-Shift-Enter instead of just Enter) to convert each text entry to a
number that SUM will sum.

That's about all I can say without knowing more about your situation.
--
Earl Kiosterud
www.smokeylake


"StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
news:u777h11h0ncqbhvp83284985erd18204ub@4ax.com...
> I've been fiddling and fiddling for some time now this evening but I
> seem to be missing something completely today. In the past I've dealt
> with adding/subtracting times formatted the same way as the end
> results, so they've worked from the get-go.
>
> In today's case, I have a spreadsheet that I'm using to calculate best
> fits for movies transferred from VHS to DVD via a DVD Recorder. My
> DVD Recorder deals with hours and minutes but my internet source gives
> total running time in minutes only.
>
> How do I get Excel 2000 to add up a column of minutes and then give
> the results in a time formatted as hours and minutes? If I could get
> this to work, I could then just plug in different movie running times
> finding best fits?
>
> Thanks! I'm in the corner with my dunce cap, I can't figure out my
> approach today in a way that works. :oD
>

On Mon, 29 Aug 2005 18:28:06 -0600, "Fred Smith"
<fredsmith99@yahoo.com> wrote:

>Take your total minutes, divide by 1440, and format as hh:mm

<geez> Thanks. As always, once one knows how, it's easy.

Works like a charm now.

>--
>Regards,
>Fred
>
>
>"StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
>news:u777h11h0ncqbhvp83284985erd18204ub@4ax.com...
>> I've been fiddling and fiddling for some time now this evening but I
>> seem to be missing something completely today. In the past I've dealt
>> with adding/subtracting times formatted the same way as the end
>> results, so they've worked from the get-go.
>>
>> In today's case, I have a spreadsheet that I'm using to calculate best
>> fits for movies transferred from VHS to DVD via a DVD Recorder. My
>> DVD Recorder deals with hours and minutes but my internet source gives
>> total running time in minutes only.
>>
>> How do I get Excel 2000 to add up a column of minutes and then give
>> the results in a time formatted as hours and minutes? If I could get
>> this to work, I could then just plug in different movie running times
>> finding best fits?
>>
>> Thanks! I'm in the corner with my dunce cap, I can't figure out my
>> approach today in a way that works. :oD
>>
>

On Mon, 29 Aug 2005 19:53:12 -0500, BenjieLop
<BenjieLop.1ujyqa_1125363905.8314@excelforum-nospam.com> wrote:

>
>StargateFan Wrote:
>> I've been fiddling and fiddling for some time now this evening but I
>> seem to be missing something completely today. In the past I've dealt
>> with adding/subtracting times formatted the same way as the end
>> results, so they've worked from the get-go.
>>
>> In today's case, I have a spreadsheet that I'm using to calculate best
>> fits for movies transferred from VHS to DVD via a DVD Recorder. My
>> DVD Recorder deals with hours and minutes but my internet source gives
>> total running time in minutes only.
>>
>> How do I get Excel 2000 to add up a column of minutes and then give
>> the results in a time formatted as hours and minutes? If I could get
>> this to work, I could then just plug in different movie running times
>> finding best fits?
>>
>> Thanks! I'm in the corner with my dunce cap, I can't figure out my
>> approach today in a way that works. :oD
>
>Assuming your minutes are entered, for example, in Cells A1:A10, the
>formula
>
>=INT(SUM(A1:A10)/60)&\" HOURS \"&MOD(SUM(A1:A10),60)&\" MINUTES\"
>
>will give you the format of the answer that you are looking for.
>
>Hope this will help you.

Thanks!

>Regards.

On Tue, 30 Aug 2005 07:29:38 -0400, "Earl Kiosterud"
<someone@nowhere.com> wrote:

>SGFan,
>
>If you want minutes and hours to take care of themselves, you must use bona
>fide time values, formatted (Format - Cells - Number) appropriately. Plain
>numbers ( like 45 for 45 minutes ) will require you to use your own formulas
>to figure out hours and minutes.

Actually, dividing by 1440 seems to have worked. I'll be using this
spreadsheet a lot and time will tell but it seems to be the simple
solution that is working so far <g>.

>If you type in the format xx:yy, it will be interpreted by Excel as hours
>and minutes. To enter minutes only, you must enter it as 0:m or 0:mm. Now
>you have a true time value, and you need only sum the cells with an ordinary
>formula, like =SUM(A2:A20), and ensure the formatting is what you need
>(Format - Cells - Custom - hh:mm).
>
>If the internet source is in plain numbers representing minutes (like 45 for
>00:45), it will have to be converted to a number, as it's probably being
>brought in as text, and Excel is **** about data typing -- text vs. numbers,
>and will have to be converted to a time-serial (divided by 24*60), before
>normal time formatting will work. You could do this with each time value
>(before they're summed), or sum the plain numbers, dividing the sum by 24*60
>and applying the formatting as before). =SUM(A2:A20)/(24*60). You may
>have to use =SUM(--A2:A20)/(24*60) entered as an arry formula
>(Ctrl-Shift-Enter instead of just Enter) to convert each text entry to a
>number that SUM will sum.
>
>That's about all I can say without knowing more about your situation.

Thank you StargateFan for asking the same question I got stuck on!

and Thank you BenjieLop and Fred!!!
I had the same question/ problem... and did a search on the Internet... this forum popped up... and voila! your help was there!
Both solutions work fantastic!
AND now I'm a member of this fantastic forum!
Annette

<<<<Re: Adding minutes showing total in hours/minutes, i.e., 60 mins + 60 mins + 15 mins to total of 2 hours 15 mins?

=INT(SUM(A1:A10)/60)&" hours "&MOD(SUM(A1:A10),60)&" minutes"

OR

Take your total minutes, divide by 1440, and format as hh:mm >>>