## Calculate min and max date among overlapping date ranges and based on a condition

I want to identify the overlap based on a criteria but now I want to know what is that min date and the max date. Any tricks up your sleeve?

The following formulas calculates the min and max date from overlapping date ranges:

**Array formula in cell F2:**

**Array formula in cell F3:**

### Explaining array formula in cell F2

**Step 1 - Find rows with overlapping dates based on condition**

(COUNTIFS($A$2:$A$9,$A$2:$A$9,$B$2:$B$9,"<="&$C$2:$C$9, $C$2:$C$9,">="&$B$2:$B$9))>1

becomes

{2;1;2;2;2;2;1;2}>1

and returns

{TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE}

**Step 2 - Check if cell range $A$2:$A$9 is equal to cell value in cell F1**

$A$2:$A$9=F1

becomes

{"A";"A";"A";"B";"C";"B";"C";"C"}="A"

and returns

{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}

**Step 3 - Filter dates**

IF(($A$2:$A$9=F1)*(COUNTIFS($A$2:$A$9,$A$2:$A$9,$B$2:$B$9,"<="&$C$2:$C$9, $C$2:$C$9,">="&$B$2:$B$9))>1, $B$2:$C$9, "")

becomes

IF({TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}, $B$2:$C$9, "")

and returns

{41275,41279;"","";41278,41280;"","";"","";"","";"","";"",""}

**Step 4 - Minimum date**

MIN(IF(($A$2:$A$9=F1)*(COUNTIFS($A$2:$A$9,$A$2:$A$9,$B$2:$B$9,"<="&$C$2:$C$9, $C$2:$C$9,">="&$B$2:$B$9))>1, $B$2:$C$9, ""))

becomes

MIN({41275,41279;"","";41278,41280;"","";"","";"","";"","";"",""})

and returns 41275 (2013-01-01) in cell F2.

### Download *.xlsx file

Min and max overlapping date based on condition.xlsx

## Calculate the min and max date from overlapping dates

The following formulas calculates the min and max date from overlapping dates:

**Array formula in cell A2:**

**Array formula in cell A4:**

### Explaining array formula in cell A2

Read this post: Filter overlapping dates from date ranges in excel

### Download excel *.xlsx file

Filter-max-min-overlapping-date-from-overlapping-date-ranges.xlsx

### Functions in this post

**COUNTIFS(**criteria_range1,criteria1, criteria_range2, criteria2...**)**

Counts the number of cells specified by a given set of conditions or criteria

**IF(**logical_test, [value_if_true], [value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

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 […]

Plot date ranges in a calendar

The image above demonstrates cells highlighted using a conditional formatting formula based on a table containing date ranges. The calendar […]

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 […]

Highlight events overlapping federal holidays

Bryan asks: i am trying to do a conditional formatting for a calendar row. (essentially, a gant chart) i have […]

Table of Contents Count all overlapping days in any number of date ranges Count overlapping days in a date range […]

Count overlapping days in multiple date ranges, part 2

In the previous post I explained how to count overlapping dates between a single date range and multiple date ranges. In […]

Find missing dates in a set of date ranges

The formula in cell B8, shown above, extracts dates not included in the date ranges, in other words, dates that […]

### 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