Thanks:  0
Likes:  0

# Thread: Time "2.17 (2min & 17 sec) to 2 17/60 min) how

1. I have a col of time in the format 2.17 that is 2 min and 17 sec. How this can be convert to 2 17/60 min.
Thank you

2. With your number in A1, you could use

=INT(A1)+(A1-INT(A1))/0.6

and apply a custom format of
# ??/60

good luck

3. On 2002-05-10 09:43, kaneson wrote:
I have a col of time in the format 2.17 that is 2 min and 17 sec. How this can be convert to 2 17/60 min.
Thank you
use the formula

=SUBSTITUTE(REPLACE(A1,LEN(A1)+1,3,"/60"),"."," ")+0

4. Hi IML:
Yours is alot neater ... beautiful!

regards

5. Thanks. I started down the same road you did, but distingushing between 3.05 and 3.5 got a little messy. I guess something along the lines of
=SUBSTITUTE(REPLACE(TEXT(A1,"0.00"),LEN(TEXT(A1,"0.00"))+1,3,"/60"),"."," ")+0

would cover that.

6. On 2002-05-10 10:17, IML wrote:
Thanks. I started down the same road you did, but distingushing between 3.05 and 3.5 got a little messy. I guess something along the lines of
=SUBSTITUTE(REPLACE(TEXT(A1,"0.00"),LEN(TEXT(A1,"0.00"))+1,3,"/60"),"."," ")+0

would cover that.
Hi IML:
If we are willing to use the Substitute|Replace formula, mine would work fine without having to make the modification you suggested; e.g.

I tried 3.05 ... and I got 3 with 3 5/60
now recall, we don't have to force 3 05/60

and then with 3.50 ... I got 3 50/60

I did not try your suggested modification -- when mine worked the way I had intended it to work, I stopped there.

Regards!

[ This Message was edited by: Yogi Anand on 2002-05-10 10:27 ]

7. Thank you, it work!!!

I work this way.

=TRUNC(E2)+(E2-TRUNC(E2))*100/60

Is there any other options other than this two

8. On 2002-05-10 10:53, kaneson wrote:
Thank you, it work!!!

I work this way.

=TRUNC(E2)+(E2-TRUNC(E2))*100/60

Is there any other options other than this two
Hi Kaneson:
Here we go:

=INT(E2)+MOD(E2,1)/60

Regards!

## User Tag List

#### Posting Permissions

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