Problem in Scheduling Sheet

Dear All,

I am working on this attached worksheet.

I am trying to do a conditional formatting for the number of working days. What I am trying to do is:

Example:

As in the row D7 I give the actual workdays required to finish the task the Gantt Chart gets a specific formatting on the right appears for the number of working days.

Problem:

I want to avoid the formatting if there are any weekend (Saturday or sunday)

Suppose I give number of working days as 20 then there would be 6 weekend days so it shall add to the overall actual working days but again sinnce we are adding 6 additional days one more weekend day would come in between so the formatting shall take care of this.

I tried all the options but

Please help

Thanks in advance

If you're saying Weekends are not valid working days then you must obviously adjust your formulae in Column E to account for this. The simplest solution to this is to use the WORKDAY function

F7: =WORKDAY($E7,$D7-1)

Note the following:
a) use of Workday requires the Analysis ToolPak (ATP) be activated (see Tools -> Addins) -- you can do this without use of ATP -- if you search this forum with daddylonglegs as poster and Workday as search criteria you should find hatfuls of examples.
b) Workday has an optional 3rd parameter - that of "holidays" so you can have a range of holidays stored in your file as a named range (_holidays) and use as 3rd argument to exclude those dates also

Once the above is done -- ie your End Date in F is correct you can then think about correcting the conditional rules which is pretty straightforward as you need simply add an additional condition which tests the WEEKDAY of the date, so this (Condition 1):

=AND(J$6>=$E7,J$6<$E7+($F7-$E7+1)*$G7%)

becomes

=AND(WEEKDAY(J$6,2)<6,J$6>=$E7,J$6<$E7+($F7-$E7+1)*$G7%)

the same logic applies to the remaining conditions.

I hope that helps.

Rajnishbhatt -- the instructions were pretty simple I think, no ?

Change the formula in F to the one I provided.

Highlight your range in which conditional formats are applied and alter the formula as per instructed by simply adding the condition in red in the example I gave you (ie I gave you the formula for Condition 1).

If still unable post back with the section causing you problems.

From FAQ section:

To mark your thread solved do the following: - Go to the first post - Click edit - Click Advance - Just below the word "Title:" you will see a dropdown with the word No prefix. - Change to Solve - Click Save