+ Reply to Thread
Results 1 to 23 of 23

convert decimal number to time : convert 1,59 (minutes, dec) to m

  1. #1
    galimi
    Guest

    RE: convert decimal number to time : convert 1,59 (minutes, dec) to m

    If the latter part of the number is decimal, then 59% of 60 minutes would be
    the answer

    1.59 minutes = 1:34 (mm:ss)

    http://www.ingenio.com/categories/ca...sp?sid=5228306

    "agenda9533" wrote:

    > How to convert decimal number to time : convert 1,59 (minutes, dec) to mm:ss


  2. #2
    agenda9533
    Guest

    convert decimal number to time : convert 1,59 (minutes, dec) to m

    How to convert decimal number to time : convert 1,59 (minutes, dec) to mm:ss

  3. #3
    Bob Phillips
    Guest

    Re: convert decimal number to time : convert 1,59 (minutes, dec) to m

    Divide it by 24 if it is hours, or by 1440 if it is minutes

    --
    HTH

    Bob Phillips

    "agenda9533" <[email protected]> wrote in message
    news:[email protected]...
    > How to convert decimal number to time : convert 1,59 (minutes, dec) to

    mm:ss



  4. #4
    agenda9533
    Guest

    Re: convert decimal number to time : convert 1,59 (minutes, dec)

    Problem is - I know how to calculate this mannualy. But I need a function
    that converts a decimal number of minutes ( for example , 1.59 ) to a time
    format mm:ss (1:48)

    Thank you for all your answers!

    "Bob Phillips" wrote:

    > Divide it by 24 if it is hours, or by 1440 if it is minutes
    >
    > --
    > HTH
    >
    > Bob Phillips
    >
    > "agenda9533" <[email protected]> wrote in message
    > news:[email protected]...
    > > How to convert decimal number to time : convert 1,59 (minutes, dec) to

    > mm:ss
    >
    >
    >


  5. #5
    JE McGimpsey
    Guest

    Re: convert decimal number to time : convert 1,59 (minutes, dec)

    One way:

    A1: 1,59
    A2: =A1/1440

    format A2 as "[m]:ss"


    In article <[email protected]>,
    "agenda9533" <[email protected]> wrote:

    > Problem is - I know how to calculate this mannualy. But I need a function
    > that converts a decimal number of minutes ( for example , 1.59 ) to a time
    > format mm:ss (1:48)


  6. #6
    agenda9533
    Guest

    Re: convert decimal number to time : convert 1,59 (minutes, dec)

    Now I got it! :-))
    Thank you!

    "JE McGimpsey" wrote:

    > One way:
    >
    > A1: 1,59
    > A2: =A1/1440
    >
    > format A2 as "[m]:ss"
    >
    >
    > In article <[email protected]>,
    > "agenda9533" <[email protected]> wrote:
    >
    > > Problem is - I know how to calculate this mannualy. But I need a function
    > > that converts a decimal number of minutes ( for example , 1.59 ) to a time
    > > format mm:ss (1:48)

    >


  7. #7
    Bob Phillips
    Guest

    Re: convert decimal number to time : convert 1,59 (minutes, dec)

    which is what I said

    --
    HTH

    Bob Phillips

    "agenda9533" <[email protected]> wrote in message
    news:[email protected]...
    > Now I got it! :-))
    > Thank you!
    >
    > "JE McGimpsey" wrote:
    >
    > > One way:
    > >
    > > A1: 1,59
    > > A2: =A1/1440
    > >
    > > format A2 as "[m]:ss"
    > >
    > >
    > > In article <[email protected]>,
    > > "agenda9533" <[email protected]> wrote:
    > >
    > > > Problem is - I know how to calculate this mannualy. But I need a

    function
    > > > that converts a decimal number of minutes ( for example , 1.59 ) to a

    time
    > > > format mm:ss (1:48)

    > >




  8. #8
    JE McGimpsey
    Guest

    Re: convert decimal number to time : convert 1,59 (minutes, dec)

    He probably got a decimal number and didn't know what to do with it.
    Just dividing doesn't format it as time.

    In article <[email protected]>,
    "Bob Phillips" <[email protected]> wrote:

    > which is what I said


  9. #9
    Bob Phillips
    Guest

    Re: convert decimal number to time : convert 1,59 (minutes, dec)

    If he knew how to calculate it manually, it would have helped if he had
    asked how he could put into a cell formula, rather than how to convert it.

    Bob

    "JE McGimpsey" <[email protected]> wrote in message
    news:[email protected]...
    > He probably got a decimal number and didn't know what to do with it.
    > Just dividing doesn't format it as time.
    >
    > In article <[email protected]>,
    > "Bob Phillips" <[email protected]> wrote:
    >
    > > which is what I said




  10. #10
    Registered User
    Join Date
    06-30-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    70

    Re: convert decimal number to time : convert 1,59 (minutes, dec) to m

    USE THIS FORMULA
    Put the 1.59 in A1 Cell and paste this in any other cell it will give u result in [mm]:ss


    =TEXT(LEFT(TEXT(A1,"0000.00"),2)&":"&MID(TEXT(A1,"0000.00"),3,2)&":"&ROUND(RIGHT(TEXT(A1,"0000.00"),2)*6/10,0),"[mm]:ss")

  11. #11
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: convert decimal number to time : convert 1,59 (minutes, dec) to m

    8 year old thread and that formula gives wrong results if the decimal part is longer than 2 digits
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  12. #12
    Registered User
    Join Date
    06-30-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    70

    Re: convert decimal number to time : convert 1,59 (minutes, dec) to m

    Sir it will converted all hours into minutes and seconds as per que. ([mm]:ss)

  13. #13
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: convert decimal number to time : convert 1,59 (minutes, dec) to m

    you divide decimal mins by 1440 that's it nothing else needed

  14. #14
    Registered User
    Join Date
    06-30-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    70

    Re: convert decimal number to time : convert 1,59 (minutes, dec) to m

    Then Please tell me what we do if value is 1212.59 instead of 1.59

  15. #15
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: convert decimal number to time : convert 1,59 (minutes, dec) to m

    1212.59 minutes is 20:12:35

  16. #16
    Registered User
    Join Date
    06-30-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    70

    Re: convert decimal number to time : convert 1,59 (minutes, dec) to m

    yes thats i knw bt wht was the formula to calculate that in excel and 2nd i need in minutes and seconds only not in hrs.

  17. #17
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: convert decimal number to time : convert 1,59 (minutes, dec) to m

    1212.59/1440
    format cell as
    [mm]:ss

  18. #18
    Registered User
    Join Date
    06-30-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    70

    Re: convert decimal number to time : convert 1,59 (minutes, dec) to m

    1st how it is 20:12:35 it should b 12:12:35

    2nd what else if i needed that result in Minutes and seconds only

  19. #19
    Registered User
    Join Date
    06-30-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    70

    Re: convert decimal number to time : convert 1,59 (minutes, dec) to m

    Quote Originally Posted by martindwilson View Post
    1212.59/1440
    format cell as
    [mm]:ss
    that will give 1212:35 but ans should be 732:35 (12*60+12)=732 minutes

  20. #20
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: convert decimal number to time : convert 1,59 (minutes, dec) to m

    no thats not the question here, the question is decimal minutes to seconds NOT 1212.59 where 1212 is representing 12 hrs 12 minutes
    1212 mins is 20.2 hours read the question by the original
    poster
    see my response here where you also posted
    http://www.excelforum.com/excel-gene...2-17-30-a.html

  21. #21
    Registered User
    Join Date
    06-30-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    70

    Re: convert decimal number to time : convert 1,59 (minutes, dec) to m

    okzzz i agree with u
    please tell me why we multiply or divide with 1440 what is this trick?? , i cannot understand this trick .....

  22. #22
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: convert decimal number to time : convert 1,59 (minutes, dec) to m

    time codes are fractions of 24 hours 1 hour is 1/24 of a day 1 minute is 1/(24*60) of a day or 1/1440
    so to convert 1 minute(decimal) to the time code as a fraction of a day 1 minute /1440 =0.000694444
    when
    0.000694444
    is formatted as time hh:mm:ss
    it gives 00:01:00

  23. #23
    Registered User
    Join Date
    06-30-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    70

    Re: convert decimal number to time : convert 1,59 (minutes, dec) to m

    okzzz now i got it thanks for reply

+ 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