Conditional format

M

miked

Hi. I am trying to set a conditional format.

If NETWORKDAYS(A1,TODAY())> 3

I would like the cell to format with red shading.

How do is set the formula in the condtional statement? I
tried ="NETWORKDAYS(A1,TODAY())>3" , but this doesn't seem
to work.

Thanks,
Mike.
 
N

Norman Harker

Hi Mike!

It doesn't work because the NETWORKDAYS function is in another
workbook (the Analysis ToolPak add-in).

You can use a workaround by putting your condition in a separate cell
(eg B1) and then referring to that cell:

B1
=NETWORKDAYS(A1,TODAY())

Then your conditional format for A1 becomes:

=B1>3

Alternatively, you can use a conditional format formula that avoids
the Analysis ToolPak

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&TODAY())),2)<=5),1-COUNTIF(Holidays,ROW(INDIRECT(A1&":"&TODAY()))))>3

(Holidays is a reference to a range that contains the dates of
holidays.)
 
A

AlfD

Hi!

If you are wanting the cell containing this formula to change format
try "Cell value is " "equal to" and False or True for the value.

If your date calculation misbehaves, have a look at the Help file whic
suggests there can be problems with dates entered as text and recommend
using DATE(2004,7,27) and such likes.

Al
 
Top