Special format a cell

[reno99]

I want to format a cell so that when you enter a swimming time of 2minutes 10.45 seconds it shows up as either: 2:10.45 or 2 min 10.45 sec in the cell. Can't get the right special format code. Thanks in advance.

[Tony Valko]

Format the cell as m:ss.00

You'll still have to enter the time as h:m:ss.00

For example, type in:

0:2:10.45

The cell will display as 2:10.45

[FDibbins]

A variation on Tony's...
mm "min" ss.00 "sec"

[reno99]

This is what I found out. Thanks for your post though. #"min" ##"."##"sec"
when I enter 12345 only it shows as 1min23.45sec

[benishiryo]

hi there reno. if you wish to type "2minutes 10.45 seconds" & return those, you need either a formula in another cell OR VBA. format cells will not help you. you would have to take Tony's or FDibbins' suggestion to type as such:
0:2:10.45

type in 1jan2013 in A1. and 12 pm in B1. Format both cells to General. you will see that A1 is 41275 (meaning 41,275th day from 1 Jan 1900) & B1 is 0.5 (1/2 a day). so when you didn't follow their suggestion & use #"min" ##"."##"sec", the formatting simply splits your whole number into:
#"min" ##"."##"sec"