Author: Oscar Cronquist Article last updated on April 30, 2021

Count overlapping days in multiple date ranges part

In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. In this article, I will demonstrate how to count overlapping dates across multiple date ranges.

The date ranges are in columns A and B. The calendar to the right is there so that you can easily verify that the formula is correct.

1. Count the number of overlapping date ranges for each date

Count overlapping days in multiple date ranges part

The first formula returns an array that counts the number of overlapping date ranges for each date. It is shown in cell range D12:Q12.

=MMULT(TRANSPOSE($A$2:$A$11^0), (TRANSPOSE(MIN($A$2:$A$11)+ROW(A1:INDEX($A:$A, MAX($B$2:$B$11)-MIN($A$2:$A$11)+1))-1)>=$A$2:$A$11)*((TRANSPOSE(MIN($A$2:$A$11)+ROW(A1:INDEX($A:$A, MAX($B$2:$B$11)-MIN($A$2:$A$11)+1))-1))<=$B$2:$B$11))

The formula returns this array: {1, 2, 1, 3, 1, 2, 2, 1, 0, 1, 1, 4, 3, 1}

Date 2005-01-01 is overlapped once by date range 2005-01-01/2005-01-02. So the first value in the array is 1.
Date 2005-01-02 is overlapped twice by date range 2005-01-01/2005-01-02 and 2005-01-02/2005-01-04. The second value in the array is 2.
...
Date 2005-01-09 is not overlapped at all so the ninth value in the array is 0 (zero).
And so on.., you can verify these numbers against the calendar above.

Back to top

2. Count dates overlapped by two or more date ranges

Count overlapping days in multiple date ranges part

The second array formula returns an array that indicates if a date is overlapped by two or more date ranges. It is entered in cell range E14:R14.

=(MMULT(TRANSPOSE($B$3:$B$12^0), (TRANSPOSE(MIN($B$3:$B$12)+ROW(A1:INDEX($A:$A, MAX($C$3:$C$12)-MIN($B$3:$B$12)+1))-1)>=$B$3:$B$12)*((TRANSPOSE(MIN($B$3:$B$12)+ROW(A1:INDEX($A:$A, MAX($C$3:$C$12)-MIN($B$3:$B$12)+1))-1))<=$C$3:$C$12))>1)*1

This formula returns {0, 1, 0, 1, 0, 1, 1, 0, 0, 0, 0, 1, 1, 0}

If we sum this array we get the total number of overlapped dates. That value is shown cell D15 and R13.

Back to top

2.1 Explaining formula in cell range D13:Q13

Step 1 - Find latest date in end dates

The MAX function returns the largest number from a cell range or array.

MAX($C$3:$C$12)

becomes

MAX({38356; 38360; 38358; 38365; 38366; 38354; 38365; 38356; 38359; 38364})

and returns 38366. Note that these numbers are actually Excel dates, numbers formatted as dates. 38366 is 1/14/2005.

Step 2 - Find earliest date in start dates

The MIN function returns the largest number from a cell range or array.

MIN($B$3:$B$12)

becomes

MIN({38354; 38359; 38356; 38362; 38364; 38353; 38364; 38356; 38358; 38364})

and returns 38353.

Step 3 - Subtract dates

MAX($C$3:$C$12)-MIN($B$3:$B$12)+1))+1

becomes

38366 - 38353 +1  equals 14.

Step 4 - Create a cell reference

The INDEX function is also able to create a cell reference.

INDEX($A:$A, MAX($C$3:$C$12)-MIN($B$3:$B$12)+1)

becomes

INDEX($A:$A, 14)

and returns cell reference A14.

Step 5- Concatenate cell references

You need to use the ampersand character & to concatenate strings, however, not necessary in this rare case.

A1:INDEX($A:$A, MAX($C$3:$C$12)-MIN($B$3:$B$12)+1)

returns cell reference A1:A14.

Step 6 - Calculate row numbers based on a cell reference

The ROW function returns a row number from a cell reference. It returns multiple row numbers if the cell reference points to a cell range.

ROW(A1:INDEX($A:$A, MAX($B$2:$B$11)-MIN($A$2:$A$11)+1))

becomes

ROW( A1:A14)

and returns {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14}.

Step 5 - Subtract array with 1

ROW(A1:INDEX($A:$A, MAX($B$2:$B$11)-MIN($A$2:$A$11)+1))-1

becomes

{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14}-1

and returns

{0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13;}

Step 6 - Add array to earliest start date

MIN($B$3:$B$12)+ROW(A1:INDEX($A:$A, MAX($C$3:$C$12)-MIN($B$3:$B$12)+1))-1

becomes

38353+ROW(A1:INDEX($A:$A, MAX($C$3:$C$12)-MIN($B$3:$B$12)+1))-1

becomes

38353 + {0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13;}

and returns

{38353; 38354; 38355; 38356; 38357; 38358; 38359; 38360; 38361; 38362; 38363; 38364; 38365; 38366}

Step 7 - Convert vertical array to a horizontal array

The TRANSPOSE function allows you to convert a vertical range to a horizontal range, or vice versa.

TRANSPOSE(array)

TRANSPOSE(MIN($B$3:$B$12)+ROW(A1:INDEX($A:$A, MAX($C$3:$C$12)-MIN($B$3:$B$12)+1))-1)

becomes

TRANSPOSE({38353; 38354; 38355; 38356; 38357; 38358; 38359; 38360; 38361; 38362; 38363; 38364; 38365; 38366})

and returns

{38353, 38354, 38355, 38356, 38357, 38358, 38359, 38360, 38361, 38362, 38363, 38364, 38365, 38366}

Step 8 - Test which dates are larger or equal to start dates

TRANSPOSE(MIN($B$3:$B$12)+ROW(A1:INDEX($A:$A, MAX($C$3:$C$12)-MIN($B$3:$B$12)+1))-1)>=$B$3:$B$12

becomes

{38353, 38354, 38355, 38356, 38357, 38358, 38359, 38360, 38361, 38362, 38363, 38364, 38365, 38366}>={38354; 38359; 38356; 38362; 38364; 38353; 38364; 38356; 38358; 38364}

and returns

{FALSE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE;FALSE, FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, TRUE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, TRUE;TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, TRUE;FALSE, FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE;FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, TRUE}

Step 9 - Test which dates are smaller or equal to end dates

TRANSPOSE(MIN($B$3:$B$12)+ROW(A1:INDEX($A:$A, MAX($C$3:$C$12)-MIN($B$3:$B$12)+1))-1))<=$C$3:$C$12

returns

{TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE;TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE;TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE;TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, FALSE}

Step 10 - Multiply arrays

(TRANSPOSE(MIN($B$3:$B$12)+ROW(A1:INDEX($A:$A, MAX($C$3:$C$12)-MIN($B$3:$B$12)+1))-1)>=$B$3:$B$12)*((TRANSPOSE(MIN($B$3:$B$12)+ROW(A1:INDEX($A:$A, MAX($C$3:$C$12)-MIN($B$3:$B$12)+1))-1))<=$C$3:$C$12)

returns

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

Step 11 - Create an array of numbers all equal to 1

Exponentiation is a mathematical operation, use the ^ character or the POWER function to calculate the result of exponentiation.

TRANSPOSE($B$3:$B$12^0)

becomes

TRANSPOSE({38354; 38359; 38356; 38362; 38364; 38353; 38364; 38356; 38358; 38364}^0)

becomes

TRANSPOSE({1; 1; 1; 1; 1; 1; 1; 1; 1; 1})

and returns {1, 1, 1, 1, 1, 1, 1, 1, 1, 1}.

Step 12 - Evaluate MMULT function

The MMULT function calculates the matrix product of two arrays, an array as the same number of rows as array1 and columns as array2.

MMULT(TRANSPOSE($B$3:$B$12^0), (TRANSPOSE(MIN($B$3:$B$12)+ROW(A1:INDEX($A:$A, MAX($C$3:$C$12)-MIN($B$3:$B$12)+1))-1)>=$B$3:$B$12)*((TRANSPOSE(MIN($B$3:$B$12)+ROW(A1:INDEX($A:$A, MAX($C$3:$C$12)-MIN($B$3:$B$12)+1))-1))<=$C$3:$C$12))

becomes

MMULT({1, 1, 1, 1, 1, 1, 1, 1, 1, 1}, {0, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0;0, 0, 0, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 0;0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1;1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 0;0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0})

and returns

{1, 2, 1, 3, 1, 2, 2, 1, 0, 1, 1, 4, 3, 1}

Step 13 - Check if value in array is larger than 1

MMULT(TRANSPOSE($B$3:$B$12^0), (TRANSPOSE(MIN($B$3:$B$12)+ROW(A1:INDEX($A:$A, MAX($C$3:$C$12)-MIN($B$3:$B$12)+1))-1)>=$B$3:$B$12)*((TRANSPOSE(MIN($B$3:$B$12)+ROW(A1:INDEX($A:$A, MAX($C$3:$C$12)-MIN($B$3:$B$12)+1))-1))<=$C$3:$C$12))>1

becomes

{1, 2, 1, 3, 1, 2, 2, 1, 0, 1, 1, 4, 3, 1}>1

and returns

{FALSE, TRUE, FALSE, TRUE, FALSE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, FALSE}

Step 14 - Convert boolean values

(MMULT(TRANSPOSE($B$3:$B$12^0), (TRANSPOSE(MIN($B$3:$B$12)+ROW(A1:INDEX($A:$A, MAX($C$3:$C$12)-MIN($B$3:$B$12)+1))-1)>=$B$3:$B$12)*((TRANSPOSE(MIN($B$3:$B$12)+ROW(A1:INDEX($A:$A, MAX($C$3:$C$12)-MIN($B$3:$B$12)+1))-1))<=$C$3:$C$12))>1)*1

becomes

{FALSE, TRUE, FALSE, TRUE, FALSE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, FALSE}*1

and returns

{0, 1, 0, 1, 0, 1, 1, 0, 0, 0, 0, 1, 1, 0}.

Back to top

Functions in this post: MMULT, ROW, INDEX

Interested in learning more about excel formulas? Join my Advanced excel course.

Back to top