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
- Press with left mouse button on the "Home" tab
- Press with left mouse button on "Conditional Formatting" button
- Press with left mouse button on "New Rule.."
- Press with left mouse button on "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.
- Press with left mouse button on "Format.." button
- Press with left mouse button on "Fill" tab
- Select a color for highlighting cells.
- Press with left mouse button on "Ok"
- Press with left mouse button on "Ok"
- Press with left mouse button on "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.
Press with left mouse button on "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.
Get Excel *.xlsx file
Cf dates category
The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]
Question: How do I highlight dates that meet criteria using conditional formatting? Table of contents Highlight values in a column […]
The image above shows rows highlighted based on value in column C being the largest or smallest in that particular […]
This article demonstrates event code combined with Conditional Formatting that highlights overlapping date ranges based on the selected date range. […]
Color odd months Conditional formatting formula: =MOD(MONTH($B6),2) Explaining CF formula in cell B6 Step 1 - Calculate number of month The […]
The following conditional formatting formula highlights dates based on day of the week. =TEXT(B3,"DDDD")=$D$3 The TEXT function converts a value […]
Cell range B3:B14 has conditional formatting applied, the formula checks if the date is today. Conditional formatting formula: =TODAY()=B3 A […]
The image above shows conditional formatting applied to cell range C8:C20, it highlights cells containing dates that fall on Saturdays […]
Overlapping category
The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in […]
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]
In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]
The formula in cell B8, shown above, extracts dates not included in the specified date ranges, in other words, dates […]
This article demonstrates event code combined with Conditional Formatting that highlights overlapping date ranges based on the selected date range. […]
This article demonstrates formulas that calculate the number of overlapping ranges for all ranges, finds the most overlapped range and […]
This article demonstrates how to calculate overlapping numerical ranges. What is interesting to know is the fact that Excel handles […]
This article explains how to create a formula that returns the earliest (smallest) and the latest (largest) date of overlapping […]
This blog article describes how to extract coinciding date ranges using array formulas, the image above shows random date ranges […]
The picture above shows an Excel Table with Data Validation applied. An error dialog box appears if a user tries […]
The worksheet above shows four different time ranges in column B and C, the formula in cell C10 counts the […]
Conditional Formatting categories
Excel categories
29 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.
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,
Get the 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, get the Excel *.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
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.get-digital-help.com/2013/03/25/days-contained-in-a-range-that-overlap-another-range/
Hey guys can some one help me modify the overlaping formula so that it would only get TRUE depending on one more column duplicated values? Screenshot below:
https://i.postimg.cc/7YM6Hxdm/Capture.png
Tried to modify the formula myself but my changes seem to not have any effect on the outcome.
=SUMPRODUCT((C$2=T_LEAVE[END DATE])*(B$2=T_LEAVE[EMPLOYEE_NAME]))>1
Tadas,
There seems to be nothing wrong with your formula, however, the image shows cell G8 selected and the formula references cells on row 2. Why is it not referencing cells on the same row (row 8)?
Dear Oscar,
I hope that this message finds you in good health.
Could you kindly help me out with the formula to highlight "Tool" used on different "Resource" on same "Planned Start Date" & "Planned End Date".
Thank you.
Resource Tool Total Req Hrs (Including C/O) Planned Start Date Planned End Date
UHL-A 7A/1709 30.94 5/3/2022 6/3/2022
UHL-A 7A/1709 2.58 6/3/2022 6/3/2022
UHL-A 7A/1709 6.66 6/3/2022 7/3/2022
UHL-A 7A/1709 6.22 7/3/2022 8/3/2022
UHL-A 7A/1709 1.59 8/3/2022 8/3/2022
UHL-A 7A/1709 16.59 8/3/2022 8/3/2022
UHL-A 7A/1709 8.15 8/3/2022 9/3/2022
UHL-A 7A/1709 5.02 9/3/2022 9/3/2022
UHL-B 3F/1780 7.02 7/3/2022 7/3/2022
UHL-B 3F/1780 14.09 7/3/2022 8/3/2022
UHL-B 3F/1780 13.79 8/3/2022 9/3/2022
UHL-B 3F/1780 13.8 9/3/2022 9/3/2022
UHL-C 7A/1709 16.91 7/3/2022 8/3/2022
UHL-C 7A/1709 5.46 8/3/2022 8/3/2022
UHL-C 3F/1780 12.14 6/3/2022 7/3/2022
UHL-C 3F/1780 12.14 7/3/2022 7/3/2022
UHL-C 3F/1780 1.67 7/3/2022 7/3/2022
UHL-C 3F/1780 5.78 9/3/2022 9/3/2022
Dear Oscar,
I hope that this message finds you in good health.
Could you kindly help me out with the formula to highlight same "Tool" in two difference "Resource" within overlapping "Planned Start Date" & "Planned End Date".
Thank you.
Resource Tool Total Req Hrs (Including C/O) Planned Start Date Planned End Date
UHL-A 7A/1709 30.94 5/3/2022 6/3/2022
UHL-A 7A/1709 2.58 6/3/2022 6/3/2022
UHL-A 7A/1709 6.66 6/3/2022 7/3/2022
UHL-A 7A/1709 6.22 7/3/2022 8/3/2022
UHL-A 7A/1709 1.59 8/3/2022 8/3/2022
UHL-A 7A/1709 16.59 8/3/2022 8/3/2022
UHL-A 7A/1709 8.15 8/3/2022 9/3/2022
UHL-A 7A/1709 5.02 9/3/2022 9/3/2022
UHL-B 3F/1780 7.02 7/3/2022 7/3/2022
UHL-B 3F/1780 14.09 7/3/2022 8/3/2022
UHL-B 3F/1780 13.79 8/3/2022 9/3/2022
UHL-B 3F/1780 13.8 9/3/2022 9/3/2022
UHL-C 7A/1709 16.91 7/3/2022 8/3/2022
UHL-C 7A/1709 5.46 8/3/2022 8/3/2022
UHL-C 3F/1780 12.14 6/3/2022 7/3/2022
UHL-C 3F/1780 12.14 7/3/2022 7/3/2022
UHL-C 3F/1780 1.67 7/3/2022 7/3/2022
UHL-C 3F/1780 5.78 9/3/2022 9/3/2022