Instead of a formula you could use a custom number format:

[=0 1/1440]m:ss.00;ss.00

then calculations should work fine as this only changes the way the
number is displayed. Or you could use the formula

= TEXT(A2,"[=0 1/1440]m:ss.00;ss.00")

Note: "0 1/1440" is a fraction representing 1/(24*60) of a day i.e. 1
minute.

On Feb 15, 3:44 pm, Time Bandit wrote:
Thanks again,

I have a new problem - SUM and AVERAGE give inconsistant results when
applied to cells with these different formats.

I have found however that if I format all cells as m.ss.00, minutes larger
than 10 are accommodated. As I am unlikely to neeed to use hours this may be
an acceptable compromise

"Teethless mama" wrote:
Yes, and Yes. If you want to calculate with another function then try this:

=IF(A2<--"0:1:00",--TEXT(A2,"ss.00"),--TEXT(A2,"m:ss.00")

the format won't always be the same as you described above

"Time Bandit" wrote:

Thanks for your help,

Am I right in thinking that this method removes the number formatting? If
so I will be unable to perform numerical operations such as MIN or LARGE?

"Teethless mama" wrote:

=IF(A2<--"0:1:00",TEXT(A2,"ss.00"),TEXT(A2,"m:ss.00"))

"Time Bandit" wrote:

Hi,
I am trying to change the way excel displays times based on the value of a
cell.

Example: If a cell is custom formatted to show m:ss.00, I am trying to find
a way to say

IF 9:59.99 THEN format = mm:ss.00

Mostly I am trying to get rid of unwanted zeros to make it easier to read
such that

00:15.25 displays as 15.25
03:15.25 displays as 3:15.25
34:15.25 displays as 34:15.25 (i.e unchanged)

Ideally I want to apply a single formula to all cells.

Your help is appreciated.