## Days contained in a range that overlap another range

Hi Oscar,

I need a fomula that gives me the number of days contained in a range that overlap another range... not sure if that is clear enough...

### Answer:

I got this from Carol Weideman, thank you! I think it is really simple and clever.

Date range 1: A to B

Date range 2: C to D

Formula: =MEDIAN(A,B+1,D+1)-MEDIAN(A,B+1,C)

**MEDIAN(**number1, [number2], ..**)**

*Returns the median, or the number in the middle of the set of given numbers*

### Example 1 - No overlapping date ranges

=MEDIAN(A,B+1,D+1) - MEDIAN(A,B+1,C)

becomes

=MEDIAN(10,15+1,8+1)-MEDIAN(10,15+1,5)

becomes

=MEDIAN(10,16,9)-MEDIAN(10,16,5)

becomes

10 - 10 = 0

0 days are overlapping.

### Example 2 - Partially overlapping date ranges

=MEDIAN(A,B+1,D+1) - MEDIAN(A,B+1,C)

becomes

=MEDIAN(10,15+1,10+1)-MEDIAN(10,15+1,7)

becomes

=MEDIAN(10,16,11)-MEDIAN(10,16,7)

becomes

11 - 10 = 1

1 day is overlapping

### Example 3 - Overlapping date ranges

=MEDIAN(A,B+1,D+1) - MEDIAN(A,B+1,C)

becomes

=MEDIAN(10,15+1,14+1)-MEDIAN(10,15+1,11)

becomes

=MEDIAN(10,16,15)-MEDIAN(10,16,11)

becomes

15 - 11 = 4

4 days are overlapping

### Example 4 - Partially overlapping date ranges

=MEDIAN(A,B+1,D+1) - MEDIAN(A,B+1,C)

becomes

=MEDIAN(10,15+1,18+1)-MEDIAN(10,15+1,15)

becomes

=MEDIAN(10,16,19)-MEDIAN(10,16,15)

becomes

16 - 15 = 1

1 day is overlapping

### Example 5 - No overlapping date ranges

=MEDIAN(A,B+1,D+1) - MEDIAN(A,B+1,C)

becomes

=MEDIAN(10,15+1,20+1)-MEDIAN(10,15+1,17)

becomes

=MEDIAN(10,16,21)-MEDIAN(10,16,17)

becomes

16 - 16 = 0

0 days are overlapping

### Download example file

How to use the MEDIAN function

The MEDIAN function calculates the median based on a group of numbers. The median is the middle number of a […]

Identify overlapping date ranges

The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in […]

Highlight records based on overlapping date ranges and a condition

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

Use MEDIAN function to calculate overlapping ranges

I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it may be dates, […]

Identify rows of overlapping records

cwrbelis asks: Hi Oscar, Great website! Keep up the good work. I have a question as to how to expand […]

Count overlapping days in multiple date ranges

The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two 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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form