Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Sep 2001
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Summing of music running times (2002 SP2)

    Looks like the search function here is still "off", so I guess I'll have to post my question.

    I trying to enter a column of music track running times, e.g. 3:48 (3 minutes and 48 seconds) that can be summed at the bottom in the correct format (e.g., total time of the CD is 67 minutes 22 seconds in the format mm:ss).

    I entered the first track's running time at 3:04, but a) it's rounding it off to 3:00, and [img]/forums/images/smilies/cool.gif[/img] when I right click the cell and choose format, it's formatted as a custom number as mm:ss, however, Excel seems to treat that as a time of day (3:03:00 AM) rather than a duration.

    Can anybody walk me through how to accomplish this?

    Much obliged.

    Paul

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Summing of music running times (2002 SP2)

    <P ID="edit" class=small>(Edited by sdckapr on 09-Sep-03 15:22. Added Postscript)</P>Format the total elapsed time in [m]:ss so the time can be over 60 min.
    mm:ss is for TIMES and you can not have a minute in time over 60.


    See here for a discussion by Chip Pearson on how excel stores time.
    Steve

    PS. Also if you are formatting as mm:ss for individuals, you probably have to enter the time in as 0:03:04 otherwise excel will think you mean 3 hrs and 4 min

    (NOTE: you could just format as hh:mm and [h]:mm and use hours/min instead of min/sec, it will make entering values easier, for CD times it should not make a real big difference.)

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Summing of music running times (2002 SP2)

    Use the following as your custom format :

    <big>[mm]:ss</big>

    and when entering your values include the hour section, i.e to enter 3 minutes and 48 seconds type in

    <big>00:3:48</big>


    Andrew C

  4. #4
    Star Lounger
    Join Date
    Sep 2001
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing of music running times (2002 SP2)

    Thanks Andrew and Steve, you are both right on the money as far as the formatting, and also the need for entering the "long-form" version of the times - it won't add properly per my requirements unless you do that.

    Interestingly, although the math works on the cell with the formula, the constituent cells, if you click any one of them (say 05:51), up top it still reads as if its time of day (12:05:51 AM).

    The link to Chris Pearson's site on excel is also appreciated. Looks like a wealth of info there.

    Thanks again.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Summing of music running times (2002 SP2)

    excel stores all its time values as time of day. If you format it as general you will see the actual value excel uses. Time values are in fact Fractions of a day. 0.5 = Noon, 0.75 = 6PM.

    Since it is FRACTIONS of day to get decimal of times:
    Multiply the value by 24 to get days
    Multiply the value by 24*60 to get hours
    Multiply the value by 24*60*60 to get minutes
    Multiply the value by 24*60*60*60 to get seconds

    Steve

Posting Permissions

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