+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    StargateFan
    Guest

    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


  2. #2
    Forum Contributor
    Join Date
    06-23-2004
    Location
    Houston, TX
    Posts
    571
    Quote Originally Posted by 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. :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
    Houston, TX

  3. #3
    Fred Smith
    Guest

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

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

    --
    Regards,
    Fred


    "StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
    news:[email protected]...
    > 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
    >




  4. #4
    Earl Kiosterud
    Guest

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

    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:[email protected]...
    > 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
    >




  5. #5
    StargateFan
    Guest

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

    On Mon, 29 Aug 2005 18:28:06 -0600, "Fred Smith"
    <[email protected]> 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:[email protected]...
    >> 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
    >>

    >



  6. #6
    StargateFan
    Guest

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

    On Mon, 29 Aug 2005 19:53:12 -0500, BenjieLop
    <[email protected]> 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.



  7. #7
    StargateFan
    Guest

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

    On Tue, 30 Aug 2005 07:29:38 -0400, "Earl Kiosterud"
    <[email protected]> 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.



  8. #8
    Registered User
    Join Date
    01-07-2006
    Posts
    1

    Thumbs up Thank you StargateFan, BenjieLop and Fred!!!

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

  9. #9
    Forum Contributor
    Join Date
    06-23-2004
    Location
    Houston, TX
    Posts
    571
    Thank you for the feedback, Annette. This forum is indeed awesome.

    Regards.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1