Working with overlapping date ranges
Today's blog post is about date ranges, the techniques demonstrated here can also be applied to time or other numerical ranges. The MMULT function is a great excel function, it allows you to do really amazing calculations with date ranges. Yes, I have said that before.
For simplicity, in this example there are only 4 date ranges in column B and C and you can see their length in the chart to the right.
Let's begin with a simple formula, it will be helpful for you if you understand this one. It calculates the number of date ranges that overlaps the first date range:
It returns 3 overlapping date ranges, which is correct. Range 2, 3 and 4 overlap range 1.
Read this post if you want to know more about this formula:
Find overlapping date ranges.
Calculate all ranges
How do we calculate the number of overlapped ranges for all ranges in one formula?
This array formula returns an array: {3;2;2;1}. The first date range has 3 overlapping ranges, described above. The second has 2, range 1 and 3 overlaps range 2. The third has 2 overlapping ranges, range 1 and 2. The fourth range has 1 overlapping date range, range 1.
In the animated picture below the drop down list in cell D7 allows you to select a date range, cell D8 tells you how many overlapping date ranges the selected date range has.
I applied conditional formatting to easily spot the selected date range.
Array formula in cell D8:
Find most overlapped date range
Using this technique we can now construct a formula that finds the most overlapped date range, array formula in cell B11:
Here is the formula for the least overlapped date range, cell B14:
Want to know more about the excel functions used in the formulas above?
SUMPRODUCT, MMULT, INDEX, MATCH
Interested in learning more about excel, join my Advanced excel course.
Download excel *.xlsx file
Compare
jeyner asks: I need to count in a list the interval between the same value. Example list, 1-2-3-1-4-5-1-6-7-8-9-7-8-1 So the answer must be for the value 1 the spaces are 2,2,6. thank you Answer: Array Formula in cell E2: =SMALL(IF($A$1:$A$14=$C$2, […]
Question: How can I compare two columns to find common values? Array formula in C2: =INDEX($A$2:$A$11, SMALL(IF(COUNTIF($B$2:$B$11, $A$2:$A$11), MATCH(ROW($A$2:$A$11),ROW($A$2:$A$11)), ""), ROWS($A$1:A1))) How to create an array formula Select cell C2 Click in formula bar Copy Paste array formula to formula […]
cwrbelis asks: Hi Oscar, Great website! Keep up the good work. I have a question as to how to expand this to the next step. Per your initial example we can see that Jeff in Row 1 and Shaun in […]
Dates
Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/09-1/10/09 Cell B1 1/11/09-1/17/09 Cell C1 1/18/09-1/24/09 How do I create a formula to do this? Answer: See row 3 and formula below […]
Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download excel file Lookup all values and find max date Lookup and find last date using multiple conditions Lookup and find […]
Table of contents Match a date when a date range is entered in a single cell Match a date when a date range is entered in two cells Use VLOOKUP to search date in date ranges and return value on […]
Mmult
Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to total? (2): Multiple Solutions. The MMULT function is used in really clever way, I thought that function was pretty much useless. […]
Liam asks: Hello Oscar, What code is needed to cause cells in Columns F - I to fill with the contents of Columns C - E when a cell in Column B includes a numeric value? Answer: The data set above […]
Here comes another post about the MMULT function, today I made a dynamic scoreboard. There are five women competing and there are 8 rounds. Look what happens with the scoreboard as I type new values in round 3.
Range
Jim asks: I downloaded the file lookup-vba3. I think I can use this to help me populate a calendar.I substituted dates for Pen, Paper, and Eraser. I then had locations substituted for $ values.Where I have a date of say, […]
This article explains how to build an array formula that sums ranges. Example, I want to know how to calculate the total sum between x.2 -x.3 and x.5-x.8 and the range is 0.5 and 4.5. This picture shows you how to […]
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 […]
Sumproduct
In this post, I will provide a formula to sum values in column (Qty) where an column (Date) meets two date criteria and an additional criterion in an adjacent column (Product). I have colored the cells in column Qty that […]
Question: Hi, What type of formula could be used if you weren't using a date range and your data was not concatenated? ie: Input Value 1.78 should return a Value of B as it is between the values in Range1 […]
Today's blog post is about date ranges, the techniques demonstrated here can also be applied to time or other numerical ranges. The MMULT function is a great excel function, it allows you to do really amazing calculations with date ranges. Yes, I […]
Transpose
The transpose function allows you to convert a vertical range to a horizontal range, or vice versa. A vertical range is a range with values in one column, a horizontal range has values in one row. You can also transpose a range […]
Today's blog post is about date ranges, the techniques demonstrated here can also be applied to time or other numerical ranges. The MMULT function is a great excel function, it allows you to do really amazing calculations with date ranges. Yes, I […]
- Download free PDF Excel guide
- Weekly new posts
- Excel tips and tricks
- Step by step tutorials
- VBA macros & custom functions
- Charts & Graphs
One Response to “Working with overlapping date ranges”
Leave a Reply
How to add a formula to your comment:
<code>your formula</code>
How to add VBA code to your comment:
[vb 1="vbnet" language=","]
VBA code
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.
Hey! Awesome article. Instead of finding the most overlapped date, how would you find the largest number of overlaps (i.e. count of the overlaps on the date instead of the date)?