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

kaneson

New Member
Joined
May 9, 2002
Messages
3
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
With your number in A1, you could use

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

and apply a custom format of
# ??/60

good luck
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Thank you, it work!!!

I work this way.

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

Is there any other options other than this two
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top