Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Convert seconds to MM:SS (Excel97)

    I use the following format below in a cell to give me the AvgCallTotalInSeconds in minutes and seconds. The problem is that it gives me the seconds in 1/10th of a minute. How can I convert the AvgCallTotalInSeconds to MM/SS? Thanks for the help.

    =BG34/60

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Convert seconds to MM:SS (Excel97)

    I am sure there is an easier way to do this, however, you can do the following:

    1. Use the following formula to get total minutes:
    Quotient(BG34,60) This will drop off the decimal portion of the result.

    2. Do the following to get seconds:
    (BG34/60 - Quotient(BG34,60)) *60

    If you do not have the Quotient function, go to Tools, Add Ins, and check Analysis Tool Pack.
    Regards,

    Gary
    (It's been a while!)

  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: Convert seconds to MM:SS (Excel97)

    If the value you are working is just an integer and not a time value the following formula will give a string version of what you want :<pre> =INT(BG34/60) & ":"&MOD(BG34,60)</pre>

    If you require the answer as an actual time try :<pre> =TIMEVALUE("00:" & INT(BG34/60) & ":"&MOD(BG34,60)),</pre>

    and format the cell containing the formual as mm:ss

    Both the above should display 72 as 1:12, the former being text, the latter an actual time value.

    Andrew C

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert seconds to MM:SS (Excel97)

    Use this formula:

    <pre>=BG34/86400
    </pre>


    That will convert the value in BG34 to an Excel Date/Time value (86400 is the number of seconds in one day). Then format the cell with the custom format:

    [mm]:ss

    With the brackets around the mm. That will display the time in minutes and seconds as elapsed time.
    Legare Coleman

Posting Permissions

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