Networkdays and Conditional Formatting

I'm trying to use the NETWORKDAYS() function as criteria in a conditoinal format. Keep getting a message that says you are not allowed to reference other worksheets or workbooks in conditional formatting criteria. I presume this has something to do with the NETWORKDAYS function being located in an add in (Analysis Toolpak), but I'm wondering if anyone knows anything about this? Is there a way around it? (BTW I am not referencing other worksheets or workbooks in the formula)..... Have a great day!

Originally Posted by rth98122

I'm trying to use the NETWORKDAYS() function as criteria in a conditoinal format. Keep getting a message that says you are not allowed to reference other worksheets or workbooks in conditional formatting criteria. I presume this has something to do with the NETWORKDAYS function being located in an add in (Analysis Toolpak), but I'm wondering if anyone knows anything about this? Is there a way around it? (BTW I am not referencing other worksheets or workbooks in the formula)..... Have a great day!

That's right, you can't use any Analysis ToolPak functions in conditional formatting.

One way round this is to place your NETWORKDAYS formula somewhere in the worksheet and then just reference that cell (or cells) in CF....or use a different function which doesn't require Analysis ToolPak which you can then use in Analysis Toolpak

This will give you the same result as NETWORKDAYS where start date is A1 and end date is B1

=B1-A1+1-INT((WEEKDAY(A1)+B1-A1)/7)-INT((WEEKDAY(A1-1)+B1-A1)/7)

although if you want to exclude holidays you need a slightly more complex formula......

Am a little behind the times... that was my first post to any forum (ever!) - didn't expect the interaction to occur so quickly.

I have a simple task list on a spread sheet. I wanted the rows to change colors as my tasks get one, four or seven days away from their due date - but I needed these days to be BUSINESS days.

daddylonglegs formula works beautifully. It is just a tad unwieldy, so I created a VBA function based on it and that works in CF just fine.

Now the sun is shining and the birds are singing (at least until a couple of holidays come up and I feel the need to make this thing account for those too.......)

Thanks and have a fabulous day.