Conditional formatting to exclude weekend and Bank Holidays
 

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

Conditional formatting to exclude weekend and Bank Holidays
 

In the formula in the conditional format, use the NETWORKDAYS function rather
than just (DateA-DateB).

This takes out weekedns and an optional list of holiday dates (usually best
to create a list on a separate [hidden] sheet and use a named range to refer
to this).

So you might have a condition:
=NETWORKDAYS(ForecastDate, ActualDate, HolidayList)5

play with this depending on whether you expect actual to be earlier or later
than forecast, and whether you expect a 1 or a 0 if they are the same day
(depends on your meaning of 'days between forecast and actual')
--
Adam Vero
MCP, MOS Master, MLSS, CWNA
http://veroblog.wordpress.com
http://www.meteorit.co.uk


"Paul" wrote:

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