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(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

 

[Guest]

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')