Article updated on February 20, 2018

The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges you need to use a more complicated array formula.

I have three date ranges (A2:B4) in this example and I want to count the number of days that overlap another date range (A8:B8).

Count overlapping dates

This array formula counts overlapping days for each date range in cell range A2:B4 compared to date range in cell range A8:B8.

Array formula in cell R2:R4:

=MMULT((TRANSPOSE($A$8+ROW(A1:INDEX($A:$A, $B$8-$A$8+1))-1)>=$A$2:$A$4)*(TRANSPOSE($A$8+ROW(A1:INDEX($A:$A, $B$8-$A$8+1))-1)<=B2:B4), ROW(A1:INDEX($A:$A, $B$8-$A$8+1))^0)

It returns this array: {3; 4; 1}. Date range 2005-01-03/2005-01-05 (A2:B2) has 3 overlapping dates compared to 2005-01-02/2005-01-12 (A8:B8). Date range 2005-01-07/2005-01-10 (A3:B3) has 4 overlapping dates compared to 2005-01-02/2005-01-12 (A8:B8). Date range 2005-01-12/2005-01-13 (A3:B3) has 1 overlapping date compared to 2005-01-02/2005-01-12 (A8:B8).

To count all overlapping days, array formula in cell A11:

=SUM(MMULT((TRANSPOSE($A$8+ROW(A1:INDEX($A:$A, $B$8-$A$8+1))-1)>=$A$2:$A$4)*(TRANSPOSE($A$8+ROW(A1:INDEX($A:$A, $B$8-$A$8+1))-1)<=B2:B4), ROW(A1:INDEX($A:$A, $B$8-$A$8+1))^0))

Functions in array formulas: MMULT, ROW, INDEX

Overlapping date ranges in cell range A2:B4

Keep in mind that if you have overlapping date ranges in A2:B4, overlapping dates will be counted twice or more.

Count overlapping dates1

In this example, date 9 and 10 are overlapped by 2005-01-07/2005-01-10 and 2005-01-09/2005-01-13. They are counted twice, see cell range R3:R4.

The value in cell A11 is wrong because of this, only 9 dates are overlapped. There is a formula for this scenario also but I'll save it for a future post.

Join my Advanced excel course and learn more.

Download example *.xlsx file

Count overlapping days in multiple date ranges.xlsx