Using Conditional Formatting for Date Comparison between 2 Columns

Question

I am trying to set up conditional formatting to highlight when the difference between 2 columns containing dates is greater than 7 business days, but cannot figure it out. Example, I would like it to highlight the cell that contains 7/27/18 when compared to the cell that contains 7/14/18 since the difference is greater than 7 business days. Any suggestions?

Answer 1

Since you want any difference greater than 7 BUSINESS days, you will need a list of bank holidays to exclude:

The formula entered in the conditional formatting is:

=OR(NETWORKDAYS(B2,A2,$I$2:$I$3)>7,NETWORKDAYS(B2,A2,$I$2:$I$3)<-7)

NETWORKDAYS Calculates the difference between the two dates, excluding weekends and any bank holidays supplied.

NOTE: It does include both supplied dates, so NETWORKDAYS("23/07/18","24/07/18") would = 2. Hopefully, this is what you want. If not, you can adjust accordingly.

The OR function simply checks that the difference is not greater than 7 OR less than -7 (in the case that the Start Date is later than the Finish Date, since you didn't specify if that was a possibility or not).