Excel Number Format Denominations

[CaveT170]

I have a number format, ' mm:ss.00 ', which allows me to enter numbers in minutes, seconds and tenths and hundredths of seconds. This works fine, but when I have a time that's less than one minute, i.e. 12.34 seconds, I still have to add ' 00: ' at the beginning, otherwise it's converted into some obscure decimal. Is there any way I can enter numbers both as ' ss.00 ' and ' mm:ss.00 ', and have them be able to interact with each other, like in the form of averages etc. ?

[#2]

@CaveT170 No - Excel sees 12.34 as a number, not as a time, and formatted as a time, that number looks very different.

To make Excel convert 12.34 to 00:12.34 would require VBA code (and disable undo). That might be overkill. Do you really want that?

[#3]

@Hans Vogelaar 

In that case, is there a way I can enter numbers with the ' 00: ' prefix, but have it be displayed without, and for numbers over a minute include the prefix, or is that the same issue?

[#4]

@CaveT170 

Apply the following custom format to the cells:

[<0.000694444444444444]ss.00;mm:ss.00

[#5]

Thank you very much. This has worked great.