Convert decimal number AS SEEN to mm:ss

Example convert 3.03 to 03:03

(integer and decimal will never be >59. In other words this (70.64) will be an invalid number to convert.

Thanks

Assuming you have 3.03 in A2, you can use this:

=--SUBSTITUTE("0:"&A2,".",":")

Format the cell as mm:ss

Hope this helps.

Pete

EDIT: You may have to use a semicolon ( ; ) instead of the commas ( , )

Give this a try.

Please Login or Register  to view this content.

Thank you ALL for this plethora of answers !!!

Originally Posted by Tony Valko

Assuming 3.30 means 3 minutes and 30 seconds that formula returns 3:03.

It is 3.03 (3 minutes, 3 seconds) not 3.30

You can change the formula to this:

=--SUBSTITUTE("0:"&TEXT(A2,"0.00"),".",":")

to overcome that problem.

Hope this helps.

Pete

Originally Posted by Tony Valko

Yes, I know the sample number you showed us was 3.03 and the formula returns the correct result for that value.

However, if that value was 3.30 instead of 3.03 then the formula returns an incorrect result.

If 3.03 is 3 mins and 3 secs then I would assume 3.30 is 3 mins and 30 secs?

Nice catch !!!

Originally Posted by Pete_UK

You can change the formula to this:

=--SUBSTITUTE("0:"&TEXT(A2,"0.00"),".",":")

to overcome that problem.

Hope this helps.

Pete

Thanks for the update !

or try... =TIME(0,INT(A2),MOD(A2,1)*100)

Yep! This works too!
Thx