Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Adding minutes and seconds in Excel

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    PaeTec Communications
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a column of data in the form of minutes and seconds:
    ex: 424:06.0 (424 minutes and 6.0 seconds).

    I would like to add the rows to give me a total number of minutes and seconds.

    Whenever I choose the regular sum function the calculation will not work. I believe that Excel is seeing the data as time on a 24 hour clock and not minutes and seconds.

    Can you help?

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Location
    Midlands, UK
    Posts
    217
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Not sure how to do this just as a sum of a column, but you could try this. If you have the time info in A1 to A10 then put :

    =TRIM(MID(B1,1,SEARCH(":",B1,1)-1))

    in B1 & copy down. Put :

    =TRIM(MID(B1,SEARCH(":",B1,1)+1,80))

    In C1 & copy down. This will split the minutes from the hours. To show a Total in A12 put the following formula :

    =SUM(B1:B10)&":"&SUM(C1:C10)

    Hopefully this will give you what you want.

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Midlands, UK
    Posts
    217
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Slight update need. The formula should be

    =TRIM(MID(B1,1,SEARCH(":",B1,1)-1))+0

    and

    =TRIM(MID(B1,SEARCH(":",B1,1)+1,80))+0

    so that Excel thinks they are numbers.

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-24 05:58, Joechick wrote:
    I have a column of data in the form of minutes and seconds:
    ex: 424:06.0 (424 minutes and 6.0 seconds).

    I would like to add the rows to give me a total number of minutes and seconds.

    Whenever I choose the regular sum function the calculation will not work. I believe that Excel is seeing the data as time on a 24 hour clock and not minutes and seconds.

    Can you help?
    Format the cell containing SUM as...

    [m]:ss.0


    If your time values are entered as text (in cells A1:A5, for example) you could sum them using...

    {=SUM(("0:"&A1:A5)+0)}

    ...and then apply the same formatting as shown above.

    Note: This is an array formula which is entered using the Control+Shfit+Enter key combination. For more on array formulas see the Excel Help Index topic for "About array formulas and how to enter them".

    [ This Message was edited by: Mark W. on 2002-04-24 12:00 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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