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. :)
Array formula in cell D6:
How to enter an array formula
- Copy above array formula (Ctrl + c)
- Double click on cell D6
- Paste above array formula (Ctrl + v)
- Press and hold CTRL + SHIFT
- Press Enter
How to copy array formula
- Select cell D6
- Copy cell D6 (Ctrl + c)
- Select cell range E6:F6
- Paste (Ctrl + v)
- Select cell range D6:F6
- Copy (Ctrl + c)
- Select cell range D7:F12
- Paste (Ctrl + v)
Explaining array formula in celll D6
Step 1 - Compare current date range with other date ranges
Step 2 - Create an array containing row numbers except the current row number
IF(ROW($B$6:$B$12)=ROW(), "", ROW($B$6:$B$12)), "")
Step 3 - If current date range overlaps another date range, return row number
IF(($B6<=$C$6:$C$12)*($C6>=$B$6:$B$12), IF(ROW($B$6:$B$12)=ROW(), "", ROW($B$6:$B$12)), "")
Step 4 - Find the k-th smallest row number
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))
Download excel *.xlsx file
Functions in this article
IF(logical_test, [value_if_true], [value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
Returns the k-th smallest number in this data set.
Returns the rownumber of a reference