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

[kaneson]

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

[IML]

With your number in A1, you could use

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

and apply a custom format of
# ??/60

good luck

[Yogi Anand]

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

[Yogi Anand]

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

regards

[IML]

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.

[Yogi Anand]

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 ]

[kaneson]

Thank you, it work!!!

I work this way.

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

Is there any other options other than this two

[Yogi Anand]

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!