Change number format with IF statement

[Time Bandit]

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.

 

[Time Bandit]

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?

 

[Time Bandit]

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]

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

 

[Lori]

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.

 

[Time Bandit]

Thanks for that one,

Is the "0" in "0 1/1440" a placeholder for "0" days?

It strikes me that there are a lot of queries regarding the way that excel
handles times and that the implementation makes it hard work for some
relatively simple requirements i.e. performing basic opertions and choosing
display options.

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.

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

 

[Lori]

Excel doesn't recognise 1/1440, it tries to convert it to a date and
then when that fails interprets it as text, so you need to prefix by
"0 " since fractions are stored in the format "a b/c".

Thanks for that one,

Is the "0" in "0 1/1440" a placeholder for "0" days?

It strikes me that there are a lot of queries regarding the way that excel
handles times and that the implementation makes it hard work for some
relatively simple requirements i.e. performing basic opertions and choosing
display options.

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.