Conditional formatting to exclude weekend and Bank Holidays

[Paul]

Hi All

I am trying to use conditional formatting to show the difference between a
due date and a forecast date. For example, if the forecast date is up to 5
days from the due date the cell is green, if is between 6 to 10 days it
turns yellow and if it is 11 days or more it turns red. How can I
conditionally format so Saturday & Sunday (and Bank Holidays?) aren't
figured in as days?

Any help would be appreciated

Thanks

Paul

 

[Erny]

Try using the function WORKDAYS(<due date>, <forecast date>,<holidays>).

Ex. for the conditional formatting, if your forecast date is in cell A1, due
date in B1 and holidays in cells C1:C20:
Condition 1 for "red": use formula
=WORKDAYS(B1,A1,$C$1:$C$20)>11

You can use similar formulas for the other colours.

I use a french language version, hope the english equivalent is correct as
displayed.

Kind regards,
Erny

 

[Ron Rosenfeld]

Try using the function WORKDAYS(<due date>, <forecast date>,<holidays>).

Ex. for the conditional formatting, if your forecast date is in cell A1, due
date in B1 and holidays in cells C1:C20:
Condition 1 for "red": use formula
=WORKDAYS(B1,A1,$C$1:$C$20)>11

You can use similar formulas for the other colours.

I use a french language version, hope the english equivalent is correct as
displayed.

Kind regards,
Erny

Did you try out your recommendation? That's always a useful thing to do.

In Excel 2003, there is no WORKDAYS function and I don't think it is present in
2007.

If you meant NETWORKDAYS, it won't work as a conditional formatting formula in
Excel 2003 since it refers to another workbook (the VBA add-in: atpvbaen)
--ron

 

[Paul]

So, Ron do you have formula that would work? If it can't include Bank
Holiday then that will be okay.

Regards

Paul

 

[Ron Rosenfeld]

So, Ron do you have formula that would work? If it can't include Bank
Holiday then that will be okay.

Not including holidays, the following formula should work to compute the number
of working days between two dates. A1 is the earlier date, B1 the later.

=SUM(INT((WEEKDAY(A1-{2,3,4,5,6})+B1-A1)/7))

Similar to the NETWORKDAYS function, this formula counts the starting date.

Another option would be to use the NETWORKDAYS function in a hidden column, and
then have your conditional formatting formula reference the contents of that
hidden column.

e.g.

=NETWORKDAYS(forecast_date,due_date,holidays)

Then, in your conditional formattinge:

Formula Is: =cell_ref>=11 (red)
Formula Is: =cell_ref>= 6 (yellow)

Set the baseline color to be green.
--ron

 

[Erny]

Hi Ron,

Thanks for the precision, so it was NETWORKDAYS the function I should have
quoted for the English version.

Kind regards,
Erny

 

[Ron Rosenfeld]

Hi Ron,

Thanks for the precision, so it was NETWORKDAYS the function I should have
quoted for the English version.

Kind regards,
Erny

But Erny, NETWORKDAYS will not work in the conditional formatting dialog box in
versions for Excel prior to 2007.
--ron

 

[Paul]

Erny and Ron

Thank you both for your help

Regards

Paul

 

[Paul]

Hi Ron,

Thanks for your help but I could not get your suggestion to work correctly.
For example the 'Sum' formula that compares the set date and the forecast
date, when these two dates are the same, the result is 1 when I would expect
as answer if 0. Also when I enter your 'Formula is' into the conditional
formatting the cells do not change colour.

I'm obvously doing something wrong. Is there anyway I can send you my
examaple spreadsheet so that you can enter this information directly?

One other item that I forgot to mention. When a task is complete the cell
needs to be blue and contains the completion date. I don't know if there is
anyway that can be worked into the formula as well?

Appreciate your help

Paul

 

[Ron Rosenfeld]

I'm obvously doing something wrong. Is there anyway I can send you my
examaple spreadsheet so that you can enter this information directly?

mocTODenilnodlefnesorTAnor

Reverse the above, then make the obvious substitutions.
--ron

 

[Paul]

Hi Ron,

I have sent you an e-mail. If you have not received it, then please send me
one and I will return it with the spreadsheet.

Thanks

Paul

 

[Ron Rosenfeld]

Hi Ron,

I have sent you an e-mail. If you have not received it, then please send me
one and I will return it with the spreadsheet.

Thanks

Paul

Response is on its way back to you.
--ron