conditional formatting-Date

[ivan]

Hi,
I'm trying to use the conditional formatting feature with my Excel 2002
software for the first time, but I'm unsure of the values needed.
Here's my question.

I want a range of values (dates to be more specific) to be highlighted if
the date listed is older than 10 business days (or 12 calendar days).

I tried using the NOW, IF, and other fuctions, but didn't come across what I
wanted to do.

Any help?
Thanks

 

[Peo Sjoblom]

Calendar days are easy, just use

=TODAY()-A1>12

under format>conditional formatting formula is


Regards,

Peo Sjoblom

 

[Dave R.]

Select the range of dates, e.g. A1:A100. Format>conditional
formatting>formula is
=TODAY()-A1>12
or >=12 if you want.

and select your color.

 

[Biff]

Hi!

Business days = Monday thru Friday less any holidays?

This requires the Analysis ToolPak add-in to use the
NETWORKDAYS function.

If you consider holidays as not being business days you
have to create a list of those dates and include the
reference to that list in the NETWORKDAYS function.

Assume that list is in the range G1:G10.

Select the range of dates to conditional format.
Conditional formatting
Formula is: =AND(A1<TODAY()-12,NETWORKDAYS(A1,TODAY
(),G1:G10)>10)

Select your format styles.
OK out

Biff

 

[Peo Sjoblom]

You can't use ATP functions directly in conditional formatting since it is
an add-in and not located on the same sheet,
only way would be to put the formula somewhere out of view and refer to it
from the conditional formatting, assume you put
the formula in AZ1 then in conditional formatting in cell A1 use =AZ1=TRUE


--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Biff]

Good catch!

Another way, use a named formula.

Biff