Change cell formatting based on value?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

LurfysMa - 05 Sep 2005 02:40 GMT

I have a table of time interval values that range from a few seconds
to 200 days. I would like to define a custom format that will use a
"hh:mm:ss" format if the number if < 1 and a "0.00" format if the
number is >=1. I thought I saw a way to do that in the help, but now I
can't find it. Can someone point me in the right direction?

Thanks

--
Running Excel 2000 SP-3 on Windows 2000

Reply to this Message

Earl Kiosterud - 05 Sep 2005 02:57 GMT

Ma,

[>=1]0.00;[<1]hh:mm:ss;

--
Earl Kiosterud
www.smokeylake.com

>I have a table of time interval values that range from a few seconds
> to 200 days. I would like to define a custom format that will use a
[quoted text clipped - 6 lines]
> --
> Running Excel 2000 SP-3 on Windows 2000

Reply to this Message

LurfysMa - 05 Sep 2005 03:09 GMT

>Ma,
>
>[>=1]0.00;[<1]hh:mm:ss;

Perfect -- thanks.

Where the heck is that in the help?

Thanks a million...

--
Running Excel 2000 SP-3 on Windows 2000

Reply to this Message

Dave Peterson - 05 Sep 2005 13:52 GMT

I searched xl2003's help for "number format codes" and found this statement:

Conditions To set number formats that will be applied only if a number meets
a condition you specify, enclose the condition in square brackets. The condition
consists of a comparison operator (comparison operator: A sign that is used in
comparison criteria to compare two values. Operators include: = Equal to, >
Greater than, < Less than, >= Greater than or equal to, <= Less than or equal
to, and <> Not equal to.) and a value.

====
I didn't see anything in help where the number of elements is specified.

> >Ma,
> >
[quoted text clipped - 8 lines]
> --
> Running Excel 2000 SP-3 on Windows 2000

Signature

Dave Peterson

Reply to this Message

LurfysMa - 05 Sep 2005 03:22 GMT

>Ma,
>
>[>=1]0.00;[<1]hh:mm:ss;

PS: A couple of follow-up questions:

Does that override the normal 4-part formatting scheme
(positive;negative;zero;text)?

How many parts can I define?

I just tried:

[<10]0.0000;[<100]00.000

which worked. Excel changed it to:

[<10]0.0000;[<100]00.000;General

Then I tried:

[<10]0.0000;[<100]00.000;[<1000]000.00

which Excel rejected.

--
Running Excel 2000 SP-3 on Windows 2000

Reply to this Message