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.



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:

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

Adds all the numbers in a range of cells