## 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 start and end date.

#### What's on this webpage

## 1. Identify rows of overlapping records - Excel 365

The formula in cell F6 checks whether the date range specified on row 6 overlaps any of the remaining date ranges in cell range F6:D12 and then returns the corresponding row numbers. It always returns the current row number as a result of the date range always overlapping itself.

Excel 365 dynamic formula in cell F6:

### 1.1 Explaining formula

#### Step 1 - Calculate row numbers based on a cell reference

The ROW function calculates the row number of a cell reference.

Function syntax: ROW(reference)

ROW($B$6:$B$12)

returns

{6;7;8;9;10;11;12}

#### Step 2 - Check if start date is smaller than or equal to all end dates

The less than and equal signs are logical operators that let you check if a number is smaller or equal to another number.

$C6<=$D$6:$D$12

becomes

40182<={40186;40193;40200;40187;40207;40214;40182}

and returns

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}.

#### Step 3 - Check if end date is larger than or equal to all start dates

The larger than and equal signs are logical operators that let you check if a number is smaller or equal to another number.

$D6>=$C$6:$C$12

becomes

40186>={40182;40189;40196;40185;40203;40210;40181}

and returns

{TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE}.

#### Step 4 - Multiply arrays (AND logic)

The parentheses let you control the order of operation, we need to evaluate the logical operators before we multiply the arrays.

($C6<=$D$6:$D$12)*($D6>=$C$6:$C$12)

becomes

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}*{TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE}

and returns

{1;0;0;1;0;0;1}.

Boolean values are converted to their numerical equivalents: 1 - TRUE , 0 (zero) - FALSE.

#### Step 5 - Filter row numbers based on an array containing boolean values

The FILTER function extracts values/rows based on a condition or criteria.

Function syntax: FILTER(array, include, [if_empty])

FILTER(ROW($B$6:$B$12), ($C6<=$D$6:$D$12)*($D6>=$C$6:$C$12))

becomes

FILTER({6;7;8;9;10;11;12},{1;0;0;1;0;0;1})

and returns {6; 9; 12}.

#### Step 6 - Rearrange vertical values horizontally

The TRANSPOSE function converts a vertical range to a horizontal range, or vice versa.

Function syntax: TRANSPOSE(array)

TRANSPOSE(FILTER(ROW($B$6:$B$12), ($C6<=$D$6:$D$12)*($D6>=$C$6:$C$12)))

## 2. Identify rows of overlapping records - earlier versions

*Hi Oscar,*

*Great website! Keep up the good work.*

*I have a question as to how to expand this to the next step. Per your initial example we can see that Jeff in Row 1 and Shaun in Row 4 overlap their work schedule. I would like to know if there were a way, say for instance in Range E6:E12, to display Jeff’s Row number next to Shaun’s name and visa – versa. I can see how this will easily display what record numbers overlap when there is only one overlap in the range. Can we differentiate the records when there are two overlaps? Let’s say Theodor in Row 6 also has an overlap with Thomas in Row 2.*

*Now the data becomes confusing because we have to determine, from the 4 grayed rows, who overlaps with whom.*

*Can a formula return the Row value of the “matching” overlap record?*

*In Jeff’s E6 cell it would indicate “Row 4” as to the matching record, and the reverse for Shaun. Shaun’s E9 cell would indicate “Row 1” as the matching record.*

*And at the same time Theodor’s E11 cell would reflect “Row 2” for Thomas’s record and Thomas’ E7 cell would show “Row 6” for Theodor.*

*I haven’t even touched on the tougher one, such as what happens when John in Row 7 has an end date of 2010-01-07, thus overlapping with both Jeff and Shaun!*

*One step at a time. :)*

*Thanks*

The array formula in cell F6 returns the rows of overlapping date ranges for the current record. The record on row 6 overlaps with both records on rows 9 and 12.

Records on row 9 and 12 show they overlap with the record on row 6, this makes it much easier to spot overlapping records.

Formula in cell F6:

#### How to enter an array formula

You need to enter the formula in cell F6 as an array formula if you have an older Excel version than Excel 365 subscription.

- Copy above array formula (Ctrl + c).
- Double press with left mouse button on cell F6.
- Paste above array formula (Ctrl + v).
- Press and hold CTRL + SHIFT simultaneously.
- Press Enter once.
- Release all keys on keyboard.

The formula now has a beginning and ending curly bracket, like this: {=array_formula}

**How to copy array formula**

- Select cell F6.
- Press and hold left mouse button on the green dot located at the bottom right corner of the selected cell.
- Drag with the mouse as far as needed to the right.
- Press and hold left mouse button on the green dot located at the bottom right corner of the selected cell range.
- Drag with the mouse downwards as far as needed.

The formula contains relative cell references, they change when the cell is copied. You need to copy cells to get this to work.

### Concatenated row numbers

If you rather have row numbers concatenated in a single cell use the following formula.

The TEXTJOIN function is available for Excel 365 subscribers, there is also User Defined Function (UDF) on the same webpage if you own an earlier version of Excel.

### Explaining formula in cell F6

I recommend using the "Evaluate Formula" tool located on the Formula tab on the ribbon. Press with left mouse button on the "Evaluate Formula" button, a dialog box appears, see image above.

Press with left mouse button on the Evaluate button to see next calculation step.

#### Step 1 - Identify overlapping date ranges

The logical operators <> and = allow you to compare dates and find overlapping date ranges.

($B6<=$C$6:$C$12)*($C6>=$B$6:$B$12)

becomes

({TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE})*({TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE})

becomes

{1;0;0;1;0;0;1}

If you multiply boolean values you get this in Excel:

- TRUE * TRUE = 1
- TRUE * FALSE = 0
- FALSE *FALSE = 0

This is AND logic and the asterisk allows you to multiply arrays row-wise.

If you use the AND function it will apply AND logic to all values and return a single value, that is the reason we can't use it here.

We want it to return an array so we can easily identify the overlapping date ranges.

The array has the same number of values as there are date ranges, the position in the array corresponds to the position in the list of records.

#### Step 2 - Create an array containing row numbers except for the current row number

To be able to return the correct row numbers we must create an array that is equally large as the previous array.

We don't want to return the date range for the current record so we need to figure out a way to remove the current row number from the array.

IF(ROW($C$6:$C$12)=ROW(), "", ROW($C$6:$C$12)), "")

becomes

IF({6;7;8;9;10;11;12}=6, "", {6;7;8;9;10;11;12}, "")

returns

{"";7;8;9;10;11;12}

#### Step 3 - If current date range overlaps another date range then return row number

The logical expressions we built in step 1 is now used in an IF function to extract the correct row numbers of date ranges that overlap.

IF(($B6<=$C$6:$C$12)*($C6>=$B$6:$B$12), IF(ROW($B$6:$B$12)=ROW(), "", ROW($B$6:$B$12)), "")

becomes

IF({1;0;0;1;0;0;1}, {"";7;8;9;10;11;12}, "")

returns

{"";"";"";9;"";"";12}

#### Step 4 - Find the k-th smallest row number

The last step is to extract the smallest number based on where in worksheet the formula is calculated.

SMALL(IF(($B6<=$C$6:$C$12)*($C6>=$B$6:$B$12), IF(ROW($B$6:$B$12)=ROW(), "", ROW($B$6:$B$12)), ""),COLUMN(A1))

becomes

SMALL({"";"";"";9;"";"";12}, COLUMN(A1))

The COLUMN function calculates the column number based on a cell reference. The cell reference A1 is relative meaning it changes when the cell is copied to cells to the right.

SMALL({"";"";"";9;"";"";12}, COLUMN(A1))

becomes

SMALL({"";"";"";9;"";"";12}, 1)

and returns 9 in cell F6.

In cell G6 the formula changes to

SMALL({"";"";"";9;"";"";12}, COLUMN(B1))

and becomes

SMALL({"";"";"";9;"";"";12}, 2)

and returns 12 in cell G6.

### Recommended articles

### Overlapping category

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

This article demonstrates formulas that show if a date range is overlapping another date range. The second section shows how […]

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

This article demonstrates how to calculate dates in a given date range (cells B13 and B14) that don't overlap the […]

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 explains how to build an array formula that sums numerical ranges. Example, I want to know how to […]

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

This article demonstrates how to calculate overlapping numerical ranges. What is interesting to know is the fact that Excel handles […]

This article demonstrates formulas that calculate the number of overlapping ranges for all ranges, finds the most overlapped range and […]

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

## Functions in this article

More than 1300 Excel formulas

## Excel formula categories

## Excel categories

### 7 Responses to “Identify rows of overlapping records”

### 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! Took a little tweaking, but this does just what I need.

For this, I use Sumproduct with two conditions:

1. Current start date = Start data range

mma173,

Can you tell us the formula?

Hi again Oscar!

On to Step 2!

The people using this file are not "Tech" savey, in fact they don't like Excel at all. So I'm trying to make it easier for them to use. The values in D6:F12 are understandable to me, however "Non-Tech Bob" comes and ask's, "How can this cell(D6) show a 9 when there are only 7 names?"

The easiest solution would be to insert a new column at the beginning of the file and starting in cell A6, enter the text "Line 1".

A7 "Line 2"

A8 "Line 3" and so on..

Is there a way for the formula in D6 (now E6) to return the text string in column A rather than the ROW number?

(New

Column)

A B C D E F G H

LINE # NAME: START DATE: END DATE: Overlapping with lines:

Line 1 Jeff 2010-01-04 2010-01-08 Line 4 Line 7

Line 2 Thomas 2010-01-11 2010-01-15

Line 3 Tim 2010-01-18 2010-01-22

Line 4 Shaun 2010-01-07 2010-01-09 Line 1

Line 5 Simon 2010-01-25 2010-01-29

Line 6 Theodor2010-02-01 2010-02-05

Line 7 John 2010-01-03 2010-01-04 Line 1

This way I could hide the "Row and Column" headers under Options and reduce the clutter on the page.

Thanks again for your assistance!

Boy did THAT not work!

This should help.

https://s11.postimg.org/jgjaowhqr/Identifying_Overlap_Ranges.png

cwrbelis,

Is there a way for the formula in D6 (now E6) to return the text string in column A rather than the ROW number?Sure! There are two sheets in the workbook below.

Identify-overlapping-date-rangesv2.xlsx