Count overlapping days across multiple date ranges
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are specified in the Excel Table column Start and End (cell range B3:B12 and C3:C12), see image above.
To the right is a part of a calendar with date 1 to 14, the gray cells are days based on the corresponding date range. The x's are dates that overlap another date.
Row 13 contains the sum of these gray days column-wise, row 14 contains 1 or 0 (zero) indicating if the date contains a date range that overlaps another date range. Cell S14 contains a total of these overlapping dates. This is only demonstrated to show that the calculation is in fact correct.
NC asks:
Thanks a tonne, Oscar. It took me about 8 hours to work through this formula piece by piece, play with it, and come to grips with its basics. Your example was clear and very useful, and this has allowed me to do big, very useful data analysis for the company that employs me. It applies to thousands of people. You're an unsung hero.
Actually just realized that I really need what you said you'd "save for a future post" (actually total number of overlapping dates). I guess I'll try to figure that out. Still... couldn't have gotten this close without you.
Answer:
Thanks NC, here comes that "future" post. Array formula in cell D16:
To enter an array formula you copy above formula and paste to a cell. Press and hold CTRL and Shift simultaneoulsy, then press Enter once. Release all keys.
The formula is now surrounded with curly brackets, don't enter these characters yourself, they appear automatically. {=formula}
Explaining formula in cell D16
Step 1 - Create array
The power of sign ^ converts all dates in Table column Start to 1, you can also use the POWER function, however, to keep the formula as small as possible I use ^.
TRANSPOSE(Table1[Start]^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})
The TRANSPOSE function converts a vertical range to a horizontal range, or vice versa.
TRANSPOSE({1; 1; 1; 1; 1; 1; 1; 1; 1; 1})
returns
{1, 1, 1, 1, 1, 1, 1, 1, 1, 1}
This array is needed in the first argument in the MMULT function.
Step 2 - Create an array containing dates
This step calculates the dates needed based on the earliest and latest date in columns Table1[Start] and Table1[End].
(TRANSPOSE(MIN(Table1[Start])+ROW(B1:INDEX($B:$B,MAX(Table1[End])-MIN(Table1[Start])+1))-1)
becomes
(TRANSPOSE(MIN({38354; 38359; 38356; 38362; 38364; 38353; 38364; 38356; 38358; 38364})+ROW(B1:INDEX($B:$B,MAX(Table1[End])-MIN(Table1[Start])+1))-1)
becomes
(TRANSPOSE(38353+ROW(B1:INDEX($B:$B,MAX(Table1[End])-MIN(Table1[Start])+1))-1)
becomes
(TRANSPOSE(38353+ROW(B1:INDEX($B:$B,MAX({38356; 38360; 38358; 38365; 38366; 38354; 38365; 38356; 38359; 38364})-MIN(Table1[Start])+1))-1)
becomes
(TRANSPOSE(38353+ROW(B1:INDEX($B:$B,38366-MIN(Table1[Start])+1))-1)
becomes
(TRANSPOSE(38353+ROW(B1:INDEX($B:$B,38366-38353+1)))-1)
becomes
(TRANSPOSE(38353+ROW(B1:INDEX($B:$B,13+1)))-1)
becomes
(TRANSPOSE(38353+ROW(B1:INDEX($B:$B,14)))-1)
becomes
(TRANSPOSE(38353+ROW(B1:$B14))-1)
becomes
(TRANSPOSE(38353+{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14})-1)
becomes
(TRANSPOSE({38354; 38355; 38356; 38357; 38358; 38359; 38360; 38361; 38362; 38363; 38364; 38365; 38366; 38367})-1)
becomes
(TRANSPOSE(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 3 - Check which dates are later or equal to dates in Table column Start
(TRANSPOSE(MIN(Table1[Start])+ROW(B1:INDEX($B:$B,MAX(Table1[End])-MIN(Table1[Start])+1))-1)>=Table1[Start]
becomes
{38353, 38354, 38355, 38356, 38357, 38358, 38359, 38360, 38361, 38362, 38363, 38364, 38365, 38366}>=Table1[Start]
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 this array:
I have added the dates horizontally and vertically to make this picture more easy to understand.
Step 4 - Repeat step 2 and 3 with Table column End
((TRANSPOSE(MIN(Table1[Start])+ROW(B1:INDEX($B:$B,MAX(Table1[End])-MIN(Table1[Start])+1))-1))<=Table1[End]))>1)
returns this array:
Step 5 - Multiply arrays
(TRANSPOSE(MIN(Table1[Start])+ROW(B1:INDEX($B:$B, MAX(Table1[End])-MIN(Table1[Start])+1))-1)>=Table1[Start])*((TRANSPOSE(MIN(Table1[Start])+ROW(B1:INDEX($B:$B, MAX(Table1[End])-MIN(Table1[Start])+1))-1))<=Table1[End])
returns this array:
Step 6 - Sum values column-wise
MMULT(TRANSPOSE(Table1[Start]^0), (TRANSPOSE(MIN(Table1[Start])+ROW(B1:INDEX($B:$B, MAX(Table1[End])-MIN(Table1[Start])+1))-1)>=Table1[Start])*((TRANSPOSE(MIN(Table1[Start])+ROW(B1:INDEX($B:$B, MAX(Table1[End])-MIN(Table1[Start])+1))-1))<=Table1[End]))
returns
{1, 2, 1, 3, 1, 2, 2, 1, 0, 1, 1, 4, 3, 1}
Step 7 - Check if value in array is larger than 1
MMULT(TRANSPOSE(Table1[Start]^0), (TRANSPOSE(MIN(Table1[Start])+ROW(B1:INDEX($B:$B, MAX(Table1[End])-MIN(Table1[Start])+1))-1)>=Table1[Start])*((TRANSPOSE(MIN(Table1[Start])+ROW(B1:INDEX($B:$B, MAX(Table1[End])-MIN(Table1[Start])+1))-1))<=Table1[End]))>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 8 - Convert boolean values to their numerical equivalents
(MMULT(TRANSPOSE(Table1[Start]^0), (TRANSPOSE(MIN(Table1[Start])+ROW(B1:INDEX($B:$B, MAX(Table1[End])-MIN(Table1[Start])+1))-1)>=Table1[Start])*((TRANSPOSE(MIN(Table1[Start])+ROW(B1:INDEX($B:$B, MAX(Table1[End])-MIN(Table1[Start])+1))-1))<=Table1[End]))>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}
Step 9 - Sum overlapping dates
SUM((MMULT(TRANSPOSE(Table1[Start]^0), (TRANSPOSE(MIN(Table1[Start])+ROW(B1:INDEX($B:$B, MAX(Table1[End])-MIN(Table1[Start])+1))-1)>=Table1[Start])*((TRANSPOSE(MIN(Table1[Start])+ROW(B1:INDEX($B:$B, MAX(Table1[End])-MIN(Table1[Start])+1))-1))<=Table1[End]))>1)*1)
becomes
SUM({0, 1, 0, 1, 0, 1, 1, 0, 0, 0, 0, 1, 1, 0})
and returns 6 in cell D16.
Count overlapping days across multiple date ranges with condition
This formula takes into account the condition specified in cell E15 as well, the formula counts overlapping dates based on date ranges that has a cell in column A which is equal to the condition in cell B14.
Formula in cell E15:
The only difference with the formula above is this part:
($A$2:$A$11=$B$14)
It is a logical expression that returns an array containing TRUE or FALSE if equal to value in cell B14.
Count values category
This post demonstrates how to build formulas that counts unique distinct values based on criteria. The image above demonstrates an […]
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 […]
Overlapping category
This article demonstrates formulas that show if a date range is overlapping another date range. The second section shows how […]
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 […]
Excel categories
6 Responses to “Count overlapping days across multiple date ranges”
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.
[…] 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. […]
This formula is almost perfect for what I need. Is there a way to add an "If" statement so that the overlap will calculate dependent on an email address. I need to calculate overlap by person for 9000 people.
Christyn Lewandowski,
great question. I have added more content to this article that answers your question.
What about the opposite? I'd like a sum of dates that are NOT overlapping. For example: a ticket's dates of validity compared to dates of actual use, when some of the days were not included with the ticket.
Ticket start: 01/01/2010 Ticket end 01/05/2010 Actual use start: 01/04/2010 Actual use end: 01/07/2010. The result should be 2, because they used the service on Jan 6 and Jan 7, when those dates were not covered by their ticket.
Thanks!!!!!
Excel Noob,
Formula in cell F3:
=MEDIAN(B3,C3+1,E3+1) - MEDIAN(B3,C3+1,D3)
Units contained in a range that overlap another range
Oscar, this is amazing stuff! I did have a follow-up question on this method. I have data that decades - I need to calculate the quantity of days that overlap for each row (or project). For example, if I have four concurrent projects, each one as a row, I need to calculate the total quantity of days that the other three projects might overlap. This figure would need to be calculated for each row. How might you recommend proceeding? Thank you for your time!