Author: Oscar Cronquist Article last 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.

Answer:

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)

Download excel example filehighlight-duplicates-where-an-adjacent-column-is-in-a-specific-date-range.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

SUM(number1,[number2],)
Adds all the numbers in a range of cells