## Highlight duplicates where an adjacent column is in a date interval using conditional formatting in excel

*Article 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:**

- Select the range (A1:A20)
- Click "Home" tab on the ribbon
- Click "Conditional formatting"
- Click "New rule..."
- Click "Use a formula to determine which cells to format"
- Click "Format values where this formula is true" window.
- 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)
- Click Format button
- Click "Fill" tab
- Select a color
- Click OK!
- 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

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]### 2 Responses to “Highlight duplicates where an adjacent column is in a date interval using conditional formatting in excel”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Column A1 Has dates Column B as data

A1 : 1/1/2010 : 5000

A2 : 2/1/2010 : 4000

A3 : 1/1/2010 : 5000

A4 : 5/1/2010 : 5000

A5 : 6/1/2010 : 4000

From column B the values which are duplicate i want to remove only those which are on same date .

Remove Duplicates from Column B by comparing from Column A Which has date. as, if the duplication is occurred on next date it should not be counted as not a duplicate because the same data is entered in the next date.

Example : Customer has Purchased Item A on 1/1/2010

Customer has purchase Item A on 1/1/2010

I want to assume that On 1/1/2010 Item A has a duplicate

If the customer has purchased Item A on 2/1/2010 . this is not duplicate because the item is purchased on the next date.

See this post: https://www.get-digital-help.com/2010/04/12/remove-duplicates-on-same-date-in-excel/