Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Apr 2004
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    convert a fraction to a time (Excel 2000 SR-1)

    I have a number of values supplied to me as mmm.dd where mmm is minutes and dd is the fraction. I'm trying to convert that into a valid time, displayed as hh:mm:ss, so that I can do some number crunching using other values supplied in the hh:mm:ss format. Does anyone have a formula I can poach?
    E.g supplied value 144.69 should be displayed as '02:24:41'
    Cheers
    Mark

  2. #2
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: convert a fraction to a time (Excel 2000 SR-1)

    Hi, welcome to Woodys <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    If the value you are given is in A1 then in A2 you could use:

    <font color=blue>
    =INT(A1/60) & ":" & INT(MOD(A1,60)) & ":" & ROUND(60*(A1-INT(A1)),2)
    </font color=blue>

    to give the hh:mm:ss format, does this work for you ?
    Thanks,

    pmatz

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: convert a fraction to a time (Excel 2000 SR-1)

    Say that your value 144.69 is in cell A1.
    In cell B1, enter this formula: =A1/1440
    Format B1 with a time format.

    Explanation: Excel stores times as fractions of 1 day. Your time is in minutes, and there are 24 * 60 = 1440 minutes in one day. So dividing by 1440 returns the time as a fraction of 1 day. The formatting does the rest.

  4. #4
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: convert a fraction to a time (Excel 2000 SR-1)

    Fair play Hans! That's clever! <img src=/S/coffeetime.gif border=0 alt=coffeetime width=32 height=48>
    Thanks,

    pmatz

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: convert a fraction to a time (Excel 2000 SR-1)

    Your formula will work fine to display the time in the desired format, but since the result is text, not a number, it cannot be used directly in calculations. To do that, you would have to apply the TIMEVALUE function to the result:
    =TIMEVALUE(INT(A1/60) & ":" & INT(MOD(A1,60)) & ":" & ROUND(60*(A1-INT(A1)),2))
    and format it with a time format.

  6. #6
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: convert a fraction to a time (Excel 2000 SR-1)

    Ah yes, the TIMEVALUE, i realised I had only formatted it and was just looking to see how to convert this to an actual time value. Thanks again [img]/forums/images/smilies/smile.gif[/img]
    Thanks,

    pmatz

  7. #7
    New Lounger
    Join Date
    Apr 2004
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: convert a fraction to a time (Excel 2000 SR-1)

    Thanks guys; both solutions work fine but the / 1440 is the quick and simple one so that's what I'll take. Problem solved, thanks for your help
    Cheers
    Mark

Posting Permissions

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