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

Related posts:

1. Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel
2. Highlight dates within a date range using conditional formatting
3. Highlight duplicates using conditional formatting in excel
4. Highlight the second or more duplicates in two lists using conditional formatting in excel
5. Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel part 2
6. Highlight smallest duplicate value in a column using conditional formatting in excel
7. Prevent duplicates using dynamic conditional formatting in excel
8. Highlight duplicate values in a range using conditional formatting in excel
9. Highlight unique values and unique distinct values in a range using conditional formatting in excel
10. Color every second row using dynamic conditional formatting in excel