WORKDAY function and conditional formatting

I would like to add a formula with conditional formatting to a cell so that if a day entered exceeds 5 business days relative to a date in another cell, the one where the new date is entered automatically changes to red.
For example: A1 has the date 02/09/05. If I enter 02/09/20 on A2 I want it to turn to red because it exceeds 5 business days.
I know I should use the WORKDAY function but I can't get the formula right. Anyone can please help? Thanks

Try the following...

1) Format > Conditonal Formatting > Formula Is

2) Enter the following formula:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($A$1&":"&$A$2)),2)<6),--(1-ISNUMBER(MATCH(ROW(INDIRECT($A$1&":"&$A$2)),$B$1:$B$10,0))))>5

...where B1:B10 contains your list of holidays, if applicable.

3) Choose your formatting

4) Click OK

If you do not want to include the ending date in the calculation, try the following formula instead...

=(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($A$1&":"&$A$2)),2)<6),--(1-ISNUMBER(MATCH(ROW(INDIRECT($A$1&":"&$A$2)),$B$1:$B$10,0))))-1)>5

Hope this helps!

Originally Posted by alexander

I would like to add a formula with conditional formatting to a cell so that if a day entered exceeds 5 business days relative to a date in another cell, the one where the new date is entered automatically changes to red.
For example: A1 has the date 02/09/05. If I enter 02/09/20 on A2 I want it to turn to red because it exceeds 5 business days.
I know I should use the WORKDAY function but I can't get the formula right. Anyone can please help? Thanks