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 file
highlight-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