+ Reply to Thread
Results 1 to 3 of 3

Coverting numbers with decimals to minutes and seconds

  1. #1
    Registered User
    Join Date
    06-08-2011
    Location
    singapore
    MS-Off Ver
    Excel 2003
    Posts
    8

    Coverting numbers with decimals to minutes and seconds

    Hi,

    I have a set of numbers which appears to represent elapsed time in the "mm:ss.0000" format which may or may not be an Excel time format.

    Here are a few examples of the numbers with their corresponding time value in square brackets:

    6.098 [6.098]
    9.377 [9.377]

    90.557 [1:30.5574]
    92.958 [1:32.9577]

    899.803 [14:59.8033]
    905.082 [15:05.0820]

    I need a formula to convert this set of time codes into either their original format or simply the mm:ss time format, so that 905.082 becomes 15:05.0820 or simply 15:05, while 6.098 remains the same or converts to 00:06

    Any ideas?

    Thanks!

  2. #2
    Valued Forum Contributor
    Join Date
    07-17-2005
    Location
    Abergavenny, Wales, UK
    MS-Off Ver
    XL2003, XL2007, XL2010, XL2013, XL2016
    Posts
    608

    Re: Coverting numbers with decimals to minutes and seconds

    HI

    Excel stores times as fractions of a day.
    A day = 24 hours x 60 minutes x 60 seconds so =24*60*60 = 86400 seconds.

    Format the cell with the below formula as Custom> mm:ss or mm:ss.0
    and use the formula
    =A1/86400
    --
    Regards
    Roger Govier
    Microsoft Excel MVP

  3. #3
    Registered User
    Join Date
    06-08-2011
    Location
    singapore
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Coverting numbers with decimals to minutes and seconds

    It works!
    Thanks Roger

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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