Formula for conditional formatting.

[OaklandJim]

Team Mr. excel

I need to use conditional formatting to create a Gantt chart worksheet.

I am using this formula -- which works in worksheet cells --

=ISNUMBER(MATCH(TEXT(rcDayDate,"ddd"),WorkDaysInAWeek,0))

to determine if the "current" date in the Gantt chart plot is a workday.

rcDayDate is a name whose RefersTo is a basic relative column address like L$5. For a given cell within the Gantt chart range it refers to the cell containing the "current" date (the date being processed for the Gantt bar plot). WorkDaysInAWeek is merely a named range containing a list of day names that are within the workweek like "Mon","Tue","Wed","Thu" for a four day workweek.

As I said above, the formula works (returns true or false) when used in cells. But when using the formula for conditional formatting it does not seem to evaluate to true or false.

Is my formula not acceptable for conditional formatting?

I really appreciate your assistance.

Jim

 

[OaklandJim]

What a knucklehead I am. The formula works as expected. I was trying to set the cell conditional color using the Pattern Color, not the Background Color. It has been a while since I used conditonal formatting AND I'm geting old. Thanks.

 

[felixstraube]

What does your WorkDaysInAWeek named range definition look like? Is it absolute?
I tried your formula and it works fine.

Another way to check if it is a working day is:

=WORKDAY(L$5-1, 1)=L$5

And you could take holidays into account like this:

=WORKDAY(L$5-1, 1, Holidays)=L$5

being Holidays a named range of all the holidays dates.