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.

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.

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.

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.

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.

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.

"Lori" wrote:

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.

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".

On Feb 16, 9:52 am, Time Bandit wrote:
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.

"Lori" wrote:
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.