Author: Oscar Cronquist Article last updated on July 11, 2018

cwrbelis asks:

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‚~ez_euro~~ez_trade~s Row number next to Shaun‚~ez_euro~~ez_trade~s name and visa ‚~ez_euro~~ez_ldquo~ 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‚~ez_euro~~ez_trade~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 ‚~ez_euro~~ez_oelig~matching‚~ez_euro~ overlap record?
In Jeff‚~ez_euro~~ez_trade~s E6 cell it would indicate ‚~ez_euro~~ez_oelig~Row 4‚~ez_euro~ as to the matching record, and the reverse for Shaun. Shaun‚~ez_euro~~ez_trade~s E9 cell would indicate ‚~ez_euro~~ez_oelig~Row 1‚~ez_euro~ as the matching record.
And at the same time Theodor‚~ez_euro~~ez_trade~s E11 cell would reflect ‚~ez_euro~~ez_oelig~Row 2‚~ez_euro~ for Thomas‚~ez_euro~~ez_trade~s record and Thomas‚~ez_euro~~ez_trade~ E7 cell would show ‚~ez_euro~~ez_oelig~Row 6‚~ez_euro~ for Theodor.

I haven‚~ez_euro~~ez_trade~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.

Array formula in cell F6:

=IFERROR(SMALL(IF(($C6<=$D$6:$D$12)*($D6>=$C$6:$C$12), IF(ROW($C$6:$C$12)=ROW(), "", R