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

*Article last updated on July 27, 2017*

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

Highlight overlapping date ranges using conditional formatting

The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]

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

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

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

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

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

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

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