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" wrote in message
...
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

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.

Regards.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=400299

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


"StargateFan" wrote in message
...
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"
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" wrote in message
.. .
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
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"
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 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.

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

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


--
amcarlson
------------------------------------------------------------------------
amcarlson's Profile: http://www.excelforum.com/member.php...o&userid=30231
View this thread: http://www.excelforum.com/showthread...hreadid=400299

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

Regards.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=400299