Conditional Formatting to exclude weekends 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(or any other way) so Saturday & Sunday, and if possible
Bank Holidays as well, aren't figured in as days?

Any help would be appreciated

Thanks

Paul

 

[Bob Phillips]

Paul, the simplest way would be to have helper cells where 5 days, 10 days
forward was calculated using the WORKDAY function, and test these in the CF.
Unfortunately you cannot use WORKDAY in CF directly.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

Forgot to say, you can use WORKDAY in CF ... IF you have Excel 2007.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Paul]

Hi Bob, I don't have 2007 only 2003.

Can anyone else provide the formula?

Thanks

Paul

 

[Bob Phillips]

Look at my first post then and try that method.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Paul]

Hi Bob,

I do not know how to do this. An example would be most helpful.

Thanks

Paul

 

[Bob Phillips]

Paul,

Give me an example of your data, and I will knock you up an example.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Paul]

Hi Bob

I have sent you a copy of the spreadsheet to your e-mail address as I can't
seem to post a reply with an attachment.

Regards

Paul