Highlight records based on overlapping date ranges and a condition
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:
The image above demonstrates a conditional formatting formula that highlights records based on a condition (column B) and date ranges (column C and D).
Records on row 3 and 5 are highlighted, they share the same condition in column B and their date ranges overlap.
How to apply conditional formatting formula to the data set
 Select a cell range
 Click the "Home" tab
 Click "Conditional Formatting" button

Click "New Rule.."
 Click "Use a formula to determine which cells to format"

Type =SUMPRODUCT(($C3<=$D$3:$D$6)*($D3>=$C$3:$C$6)*($B3=$B$3:$B$6))>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"
Explaining CF formula in cell B3
The formula is a conditional formatting formula, however, you can enter it in cell E3. Then copy the cell and paste to cells below.
Select cell E3 and then start the "Evaluate Formula" tool on the "Formula" tab on the ribbon.
Click "Evaluate" button to go through each calculation step.
Step 1  Check if cells are equal to cell value
The equal sign lets you compare a cell value with a range. If it is the same the logical expression returns TRUE, if not FALSE.
$B3 is locked to column B, however, the row number changes as the CF formula is applied to cells below.
$B3=$B$3:$B$6
becomes
"ABC "={"ABC ";"BCD ";"ABC ";"ABC "}
and returns {TRUE;FALSE;TRUE;TRUE}.
Step 2  Check if date ranges overlap
The following two logical expressions check which date ranges overlap with the first record. The parenthes make sure that the order of calculation is correct. We want the comparisons to be performed first and then multiply the arrays.
($C3<=$D$3:$D$6)*($D3>=$C$3:$C$6)
becomes
(39814<={40178;40178;40359;39813})* (40178>={39814;39965;39995;39600})
{TRUE;TRUE;TRUE;FALSE}* {TRUE;TRUE;TRUE;TRUE}
and returns {1;1;1;0}
Step 3  Multiply arrays
($C3<=$D$3:$D$6)*($D3>=$C$3:$C$6)*($B3=$B$3:$B$6)
{TRUE;FALSE;TRUE;TRUE}* {1;1;1;0}
and returns {1;0;1;0}
Step 4  Sum values in array
SUMPRODUCT(($C3<=$D$3:$D$6)*($D3>=$C$3:$C$6)*($B3=$B$3:$B$6))
becomes
SUMPRODUCT({1;0;1;0})
and returns 2.
Step 5  Check if sum is larger than 1
SUMPRODUCT(($C3<=$D$3:$D$6)*($D3>=$C$3:$C$6)*($B3=$B$3:$B$6))>1
becomes
2>1
and returns TRUE. Cell B3 is highlighted.
Download Excel *.xlsx file
Highlight overlapping date ranges using conditional formatting
The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that [โฆ]
Highlight dates in a date range
Question: How do I highlight dates that meet criteria using conditional formatting? Table of contents Highlight values in a column [โฆ]
How to highlight MAX and MIN value based on month
The image above shows rows highlighted based on value in column C being the largest or smallest in that particular [โฆ]
Identify overlapping date ranges
The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in [โฆ]
Use MEDIAN function to calculate overlapping ranges
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it may be dates, [โฆ]
Identify rows of overlapping records
cwrbelis asks: Hi Oscar, Great website! Keep up the good work. I have a question as to how to expand [โฆ]
Count overlapping days in multiple date ranges
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges [โฆ]
25 Responses to โHighlight records based on overlapping date ranges and a conditionโ
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form
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 6Jan12 15Jan12
65153 MENDOZA INACTIVE 1Sep08 9Apr12
57456 RODRIGUEZ BACK TO WORK 15Jan12 5Jan12
57613 JOSE INACTIVE 16Mar10 8Jun11
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:
https://www.getdigitalhelp.com/wpcontent/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 13Mar2012 13Mar2012
1234 Sick 10Mar2012 18Mar2012
5678 Annual 11Feb2012 12Feb2012
5678 Annual 12Feb2012 15Feb2012
5678 Annual 09Feb2012 18Feb2012
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
CarterMahoney.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
Hi,
would love to know whether there is a formula to find how many days, two dates range, are falling in a certain period.
e.g.: start date  06/22/2017 end date  06/28/2018
I would like to create a formula which returns the number of days, between above mentioned dates range, falling in calendar year 2017 and 2018.
can you help me?
Many thanks,
Dor
Dor Cohen,
You can use the MEDIAN function to count overlapping days:
https://www.getdigitalhelp.com/2013/03/25/dayscontainedinarangethatoverlapanotherrange/