adam asks:

Hi, I have a situation where I want to count if this value is duplicate and if it the dates are overlapping as well. Is there anyway to work this out?

eg
Serial | Start date | End Date
ABC | 01.01.2009 | 31.12.2009
BCD | 01.06.2009 | 31.12.2009
ABC | 01.07.2009 | 30.06.2010

So it should list the first and third entry.

Answer:

Highlight duplicate values and overlapping dates

  1. Select a cell range
  2. Click "Home" tab
  3. Click "Conditional Formatting" button
  4. Click "New Rule.."
  5. Click "Use a formula to determine which cells to format"
  6. Type =SUMPRODUCT(($B2<=$C$2:$C$5)*($C2>=$B$2:$B$5)*($A2=$A$2:$A$5))>1 in "Format values where this formula is TRUE" window.
  7. Click "Format.." button
  8. Click "Fill" tab
  9. Select a color for highlighting cells.
  10. Click "Ok"
  11. Click "Ok"
  12. Click "Ok"

Download excel sample file for this tutorial.

duplicate values and overlapping dates.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays