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

[StargateFan]

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. D

 

[Fred Smith]

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

 

[BenjieLop]

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. D

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.

Regards.

 

[Earl Kiosterud]

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 anal 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.

 

[StargateFan]

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.

 

[StargateFan]

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.

 

[StargateFan]

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

 

[amcarlson]

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 >>

 

[BenjieLop]

Thank you for the feedback, Annette. This forum is indeed awesome.

Regards.