Highlight duplicate values and overlapping dates in excel
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
Related posts:
Highlight overlapping date ranges using conditional formatting in excel
Count overlapping dates in excel
Filter overlapping dates from date ranges in excel
Find overlapping date ranges in excel
Highlight duplicate values in a range using conditional formatting in excel
Recommended categories
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 file
Ana.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:
http://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