## Highlight duplicate values and overlapping dates in excel

*Article updated on July 27, 2017*

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

- Select a cell range
- Click "Home" tab
- Click "Conditional Formatting" button
- Click "New Rule.."
- Click "Use a formula to determine which cells to format"
- 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.
- Click "Format.." button
- Click "Fill" tab
- Select a color for highlighting cells.
- Click "Ok"
- Click "Ok"
- 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

### 23 Responses to “Highlight duplicate values and overlapping dates 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

Thanks. It works perfectly.

Hi Oscar,

Please help me.

In due date i used this formula to track if the employee already exceed in her Inactive status.

NOTE: g1=DATE TODAY

=IF(E2=$G$2+5,"5 DAYS BEFORE DUE",IF(E2=$G$2,"EXPIRED",IF(E2<$G$1,"EXCEEDED", "--"))

If ever the employee Back to work from Inactive status. I would like to erase automatic the "EXCEEDED" in column Due date.

What formula can i used? Please help me

HRID LName Status Start End Due Date

57456 RODRIGUEZ INACTIVE 6-Jan-12 15-Jan-12

65153 MENDOZA INACTIVE 1-Sep-08 9-Apr-12

57456 RODRIGUEZ BACK TO WORK 15-Jan-12 5-Jan-12

57613 JOSE INACTIVE 16-Mar-10 8-Jun-11

Ana,

Download excel *.xlsx fileAna.xlsx

Oscar,

Thank you so much for your help... This is great...

Thank you Mr.Genius

Hi Oscar,

If possible, if ever I can add Separated status in column C(Status). If ever she BACK TO WORK or SEPARATED the "EXCEEDED" in column Due date automatic erase.Please advise

Thank you

Oscar,

Please help me....What formula can i used?

Oscar,

Please help.....

Ana,

I don´t think I understand. Your first question:

If ever the employee Back to work from Inactive status. I would like to erase automatic the "EXCEEDED" in column Due date.

Your next question:

add Separated status in column C(Status). If ever she BACK TO WORK or SEPARATED the "EXCEEDED" in column Due date automatic erase.

Anyway I tried, download *.xlsx file:

https://www.get-digital-help.com/wp-content/uploads/2010/10/ANA1.xlsx

Oscar,

This is awesome formula Oscar. Thank you so much .

THANK YOU........

ah great formula Oscar! interesting...

Hi Oscar,

I have difficulty finding out the overlapping of dates. see example:

EMP ID TYPE OF LEAVE START END

1234 Sick 13-Mar-2012 13-Mar-2012

1234 Sick 10-Mar-2012 18-Mar-2012

5678 Annual 11-Feb-2012 12-Feb-2012

5678 Annual 12-Feb-2012 15-Feb-2012

5678 Annual 09-Feb-2012 18-Feb-2012

I'm checking for over 4,000 records. PLEASE HELP!

THANK YOU SO MUCH.

Lucy,

Is this what you are looking for?

Lucy.xlsx

Yes is there any formula to highlight the overlapping dates?

Thanks!

Lucy,

the formula I provided highlights overlapping dates only if the EMP ID is the same.

This formula highlights records with overlapping dates.

SUMPRODUCT(($C2< =$D$2:$D$6)*($D2>=$C$2:$C$6))>1

Hey Oscar,

I am trying to do something similar but cannot figure it out. if you could help I would really appreciate it as I need to fix our database this week & remove duplicate

Column J = Ad Id #

Column f = Date

I am trying to highlight all of the cells where the AD ID# was entered for the same date. The AD ID #'s are all unique so my logic is that there should never be the same AD ID entered into the spreadsheet with the same date.

I have tried this formula, but no luck - =SUMPRODUCT(($J2=$J$2:$J$9263))>1

Thanks for your help!Let me

* This formula

=SUMPRODUCT(($J2=$J$2:$J$9263))>1

Hmm I am not sure why the whole formula is not showing up..

I will break it up

=SUMPRODUCT(($J2=$J$2:$J$9263))>1

Carter Mahoney,

Conditional formatting formula:

=SUMPRODUCT(($A2=$A$2:$A$7)*($B2=$B$2:$B$7))>1

Carter-Mahoney.xlsx

PLEASE HOW TO CALCULATE OVERLAPING DAY IN NUMBER

FOR EX.

2/1/2012 5/1/2012

3/1/2012 4/1/2012 2 DAYS OVERLAPING

SAMAN,

read this post: Count overlapping days in a date range

Hi Oscar,

I hope this site is still active.

I'm looking for a formula that will identify/highlight duplicate names within every six week time period.

So if 'John Smith' appears twice within any six week period it would highlight his name.

I only have 2 columns, date and name.

Thanks!

Hi,

I used the above formula and it worked. However when I have some rows having blank dates, it applies the formatting also.

It works well. Exactly what i needed.Thank you so much =D