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

1. ## 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. ## 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 ?

3. ## 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. ## 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>

5. ## 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. ## 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]

7. ## 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
•