Article updated on February 28, 2014

Question: How do I highlight duplicates in a range of dates, not considering those outside the date range? See picture below. I want to find duplicates between the dates 1/1/2009 and 1/20/2009, sorting out all other dates and their adjacent data out of the equation.

The conditional formatting formula in range A2:B17:

=IF((\$B2<\$E\$2)*(\$B2>\$E\$1), SUM(IF(IF((\$B2:\$B\$2<\$E\$2)*(\$B2:\$B\$2>\$E\$1), \$A2:\$A\$2, 0)=\$A2, 1, 0))-1, 0)

How to apply the conditional formatting formula in excel 2007:

1. Select the range (A1:A20)
2. Click "Home" tab on the ribbon
3. Click "Conditional formatting"
4. Click "New rule..."
5. Click "Use a formula to determine which cells to format"
6. Click "Format values where this formula is true" window.
7. Type =IF((\$B2<\$E\$2)*(\$B2>\$E\$1), SUM(IF(IF((\$B2:\$B\$2<\$E\$2)*(\$B2:\$B\$2>\$E\$1), \$A2:\$A\$2, 0)=\$A2, 1, 0))-1, 0)
8. Click Format button
9. Click "Fill" tab
10. Select a color
11. Click OK!
12. Click OK!

Try changing dates in E1 and E2 and see different cells highlighted in column A:B.

To include the the start and the end date into the conditonal formatting formula, try this:

=IF((\$B2<=\$E\$2)*(\$B2>=\$E\$1), SUM(IF(IF((\$B2:\$B\$2<=\$E\$2)*(\$B2:\$B\$2>=\$E\$1), \$A2:\$A\$2, 0)=\$A2, 1, 0))-1, 0)