Apply Conditional Formatting to date differences without weekends or holidays

Hello,

Apologies for the length of this post.

Information:
Using Excel 2002 on WinXP SP3. ** Please note: My organisation does not allow the use of the Analysis ToolPak so I am unable to utilise Networkdays or Workdays functions and would prefer a worksheet function rather than VBA.

Situation:
A new workbook to record KPI performance is created for each month There is a sheet (named Calendar) listing public holidays. Formulas have been added to calculate the observed day for each holiday. The observed date list is in Calendar!A2:A10 and this range has been named Holidays.

There is a separate sheet for each business day within the month. These record work on hand for each date entered into A11 down and compares these to B3 to calculate KPI compliance.

Problem:
I am trying to apply Conditional Formatting to B11 on each daily sheet based around 5 business days between 2 dates. I need to allow for weekends and public holidays which occur between the 2 dates. I have attached a sample workbook.

1. The work-on-hand date is day 0 when calculating the date difference
2. 5 business days must include the date in B3
3. A11 contains the date of work on hand
4. B11 needs CF to indicate when the date difference between B3 and A11 exceeds 5 business days, equals 5 business days, or is less than 5 business days.

I have tried combining different functions (e.g. weekday, countif, datedif, sumproduct, etc) but am unable to get anything to work so I’m obviously on the wrong track or am putting them together incorrectly. I would greatly appreciate any assistance to help me put together a formula/formulas that work.

Thanks dominicb. I admit I may have seen this come up in a search but ignored it due to seeing Networkdays in the description and did not fully investigate it. I'll have a play to see if I can make it work in my scenario.

Many thanks again for your helpful pointer. If I can get it to work, I'll mark the thread as solved and post my solution.

For your example I'm not sure which is supposed to be the earlier date, is it B3?

Looking at Chip's formulas, you might have problems applying some of those in conditional formatting, for example you can't use an "array constant" like {1,7} in conditional formatting.

This formula should be OK though

=SUM((WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)<6)*(COUNTIF(holidays,ROW(INDIRECT(A2&":"&B2)))=0))

That will calculate the number of workdays between A2 and B2 inclusive, excluding holidays. It's an "array formula" but when using conditional formatting that doesn't matter, in conditional formatting no special entry is required for array formauls so they can be used like any normal formula

Thank you daddylonglegs. The earlier date is in A11. B3 is basically today's date on the day these reports are being compiled. It is the difference between the two dates which need to be flagged with CF to identify if we are within expected processing times or if we have blown our deadline. I'll certainly try your suggested formula. I had been playing with Chip Pearson's method (as suggested by dominicb) in a cell on the same worksheet and planned to refer to it in the CF window if I could get it to work.

Thanks again for your assistance.

Hello,

Many thanks to those who helped point me in the right direction.

I thought I should post my solution here in case this helps any others. To recap: I need to indicate where more than/equal to/less than 5 days occur between 2 dates allowing for weekends & observed holidays and am unable to use the ATP due to org policy and prefer not to use VBA.

These are the formulas used in CF. I have deducted 1 day as the first date is not counted towards the KPI of 5 work days:

Formula:

Please Login or Register  to view this content.

This has raised a new issue of summing cells in Col B where the number of work days <= 5. I will start a new thread for this.

Hope this helps someone.