Change Decimal to mm:ss.00 format

[clueless48]

Can anyone help? I'm new to forum and this is defeating me but cannot give up!!
I have lots of swimming time data which has been input manually using a decimal instead of a colon between minutes and seconds, how can I convert this? Changing the format to this does not work as some times have minutes and some are just seconds as below :-
The times need to be in mm:ss.ms format to be able to import into a system

[TABLE="width: 500"]
<tbody>[TR]
[TD]50[/TD]
[TD]FREE[/TD]
[TD]42.9
[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]FREE[/TD]
[TD]1.39.48[/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD]FLY[/TD]
[TD]52.36[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]IM[/TD]
[TD]2.46.35[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[mikerickson]

Have you tried the number format mm:ss.00

or the formula =TEXT(A1,"mm:ss.00")

 

[clueless48]

Yes and still wont change

 

[wideboydixon]

May not be the most efficient solution but I think this works:

Book1
	A	B	C	D
1	50	FREE	42.9	00:42.90
2	100	FREE	1.39.48	01:39.48
3	50	FLY	52.36	00:52.36
4	200	IM	2.46.35	02:46.35
Sheet1

Cell Formulas
Range	Formula
D1	=TEXT(IF(LEN(C1)-LEN(SUBSTITUTE(C1,".",""))<2,C1/86400,LEFT(C1,FIND(".",C1)-1)/1440+MID(C1,FIND(".",C1)+1,LEN(C1))/86400),"mm:ss.00")

WBD

 

[kvsrinivasamurthy]

Try this . It works.
Format cells
Custom--> mm:ss.00

 

[mikerickson]

What is the input value?
If it is column A (decimal minutes?), one formula might be =A1*60*24 and then format the cell as suggested above.