Conditional Formatting with NETWORKDAYS function

[rlaw68]

Hi everyone -- here's what I'm trying to do:

If the date in F3 is more than 2 WORK days greater than the date in E3, turn the background in F3 red.

I'm trying to use the NETWORKDAYS function and avoid a bunch of VBA if at all possible, but conditional formatting doesn't seem to allow this. Any ideas or tips?

Thanks,
Richard

[Dave Peterson]

Maybe you could use a helper cell that contains the formula with the
=networkdays() function in it.

Then refer to that cell in your Format|Conditional formatting dialog.

rlaw68 wrote:
>
> Hi everyone -- here's what I'm trying to do:
>
> If the date in F3 is more than 2 WORK days greater than the date in E3,
> turn the background in F3 red.
>
> I'm trying to use the NETWORKDAYS function and avoid a bunch of VBA if
> at all possible, but conditional formatting doesn't seem to allow this.
> Any ideas or tips?
>
> Thanks,
> Richard
>
> --
> rlaw68
> ------------------------------------------------------------------------
> rlaw68's Profile: http://www.excelforum.com/member.php...fo&userid=5744
> View this thread: http://www.excelforum.com/showthread...hreadid=561117

--

Dave Peterson

[Bob Phillips]

You could try this formula in CF

=SUMPRODUCT(INT((MAX($F$3,$E$3)-WEEKDAY(MAX($F$3,$E$3)+1-ROW(INDIRECT("2:6")
))-MIN($F$3,$E$3)+8)/7))

or

=SUMPRODUCT(INT((MAX($F$3,$E$3)-WEEKDAY(MAX($F$3,$E$3)+1-ROW(INDIRECT("2:6")
))-MIN($F$3,$E$3)+8)/7))
-SUMPRODUCT(ISNUMBER(MATCH(WEEKDAY(holidays),ROW(INDIRECT("2:6")),0))*(holid
ays>=MIN($F$3,$E$3))*(holidays<=MAX($F$3,$E$3)))

if you want holidays

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"rlaw68" <[email protected]> wrote in
message news:[email protected]...
>
> Hi everyone -- here's what I'm trying to do:
>
> If the date in F3 is more than 2 WORK days greater than the date in E3,
> turn the background in F3 red.
>
> I'm trying to use the NETWORKDAYS function and avoid a bunch of VBA if
> at all possible, but conditional formatting doesn't seem to allow this.
> Any ideas or tips?
>
> Thanks,
> Richard
>
>
> --
> rlaw68
> ------------------------------------------------------------------------
> rlaw68's Profile:
http://www.excelforum.com/member.php...fo&userid=5744
> View this thread: http://www.excelforum.com/showthread...hreadid=561117
>