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 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 […]
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 […]
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
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 […]
In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]
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 […]
Overlapping category
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
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 […]
This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]
This article demonstrates how to calculate dates in a given date range (cells B13 and B14) that don't overlap the […]
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 explains how to build an array formula that sums numerical ranges. Example, I want to know how to […]
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 […]
This article demonstrates how to calculate overlapping numerical ranges. What is interesting to know is the fact that Excel handles […]
This article demonstrates formulas that calculate the number of overlapping ranges for all ranges, finds the most overlapped range and […]
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 […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
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!