## 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 […]

How to calculate overlapping time 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

This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]

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 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

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/