One of the typical tasks for a Microsoft Excel user. We have two ranges of dates of the type “beginning-end”. The challenge is to determine whether these ranges overlap and, if so, by how many days.
Intersect or not?
Let’s start by solving the question of whether there is an intersection of intervals in principle? Suppose we have a table of work shifts for employees like this:
It is clearly seen that the work shifts of Yaroslav and Elena intersect, but how to calculate this without resorting to building a calendar schedule and visual control? The function will help us SUMPRODUCT (SUMPRODUCT).
Let’s insert another column into our table with a formula that yields the boolean value TRUE if the dates intersect:
How many days is the crossing?
If it is fundamentally not easy to understand whether our intervals intersect or not, but to know exactly how many days exactly fall into the intersection, then the task becomes more complicated. Logically, it is necessary to “pump” as many as 3 different situations in one formula:
- intervals do not overlap
- one of the intervals completely absorbs the other
- intervals partially intersect
From time to time, I see the implementation of this approach by other users using a bunch of nested IF functions, etc.
In fact, everything can be done beautifully using the function MEDIAN (MEDIAN) from category Statistical.
If we conditionally designate the beginning of the first interval as N1, and the end for K1, and the beginning of the second N2 and end for K2, then in general terms our formula can be written as:
=MEDIAN(N1;K1+ 1;K2+1)-MEDIAN(N1;K1+ 1;N2)
Compact and elegant, isn’t it? 😉
- How does Excel actually work with dates? How to calculate the number of calendar or business days between dates?
- How to build a calendar schedule (holidays, trainings, shifts…) in Excel using conditional formatting?
- Checking one or more conditions with IF (IF) functions