Count overlapping days in multiple date ranges, part 2
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.
What's on this webpage
1. Count the number of overlapping date ranges for each date
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.
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.
2. Count dates overlapped by two or more date ranges
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.
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.
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}.
Functions in this post: MMULT, ROW, INDEX
Interested in learning more about excel formulas? Join my Advanced excel course.
Related articles
The formula in cell B8, shown above, extracts dates not included in the specified date ranges, in other words, dates […]
This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]
This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values […]
Formula in C12: =SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)) How to create an array formula Double press with left mouse […]
This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]
The array formula in cell D3 calculates the number of unique distinct items based on the given date in column B. […]
The array formula in cell E3 counts unique distinct items for all dates within the same week. Example, week 2 […]
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above […]
This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]
davidlim asks: re: Count records between two dates and a criterion based on the example, i was looking for 1 […]
This article demonstrates techniques on how to count cells based on the background color. I will also demonstrate user defined […]
The formula in cell E16 counts the number of cells between value B and H, Value B is in cell B3 […]
This article demonstrates ways to count contiguous values in a column, in other words, values that repeat and are adjacent. […]
Question: My issue is that I get the date in this format: 7/23/2011 7:00:00 AM I am trying to count […]
NETWORKDAYS function returns the number of whole workdays between two dates, however the array formula I am going to demonstrate […]
Janib Soomro asks: In A column, there are dates in mmddyyy format and in B column, there are two variables […]
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in […]
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]
The formula in cell B8, shown above, extracts dates not included in the specified date ranges, in other words, dates […]
This article demonstrates event code combined with Conditional Formatting that highlights overlapping date ranges based on the selected date range. […]
This article demonstrates formulas that calculate the number of overlapping ranges for all ranges, finds the most overlapped range and […]
This article demonstrates how to calculate overlapping numerical ranges. What is interesting to know is the fact that Excel handles […]
This article explains how to create a formula that returns the earliest (smallest) and the latest (largest) date of overlapping […]
This blog article describes how to extract coinciding date ranges using array formulas, the image above shows random date ranges […]
The picture above shows an Excel Table with Data Validation applied. An error dialog box appears if a user tries […]
The worksheet above shows four different time ranges in column B and C, the formula in cell C10 counts the […]
5 Responses to “Count overlapping days in multiple date ranges, part 2”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Hi Oscar,
Thanks for your knowledge sharing and really helpful!
You have mentioned the overlapping date within a single month. My question is how can we highlight the overlapping dates for 2 months. Kindly find the details below for your reference.
Date Format is in (MM/DD/YYYY)
Start - 03/28/2016 - End - 04/05/2016
Start - 04/25/2016 - End - 05/10/2016
I need your help in this issue. It will be great if I get the solution for this and the same will reduce my work upto 50%. Requesting you to provide me the solution for my query.
Regards
Sam Fredy. P
Hi Oscar,
Please help. Awaiting for your positive reply.
Regards
Sam
Hi Oscar,
Pls help me for my query.
Regards
Sam
My question is probably better suited here (I had initially posted in it Part 1). For this example, I want to find the maximum number of overlaps that occurred with a set of date ranges (i.e. in this example above, it would be four (or another way of looking at it is I want the Max value of Row 12).
This is so close to what I want. However, what I would LOVE to see in column "R" is the total number of days that one date overlaps all others.
For instance, the date range on row has 2 days that overlap 3 other date ranges. I would like to see the formula for calculating that 2 days in Column "R"
Then I can do a "Countifs" for the number days that overlap with other date ranges where the same "resource" has been assigned.
Is this possible?