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

*Article 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

How to highlight overlapping date ranges Click "Home" tab Click "Conditional Formatting" button Click "New Rule.." Click "Use a formula […]### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**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.

**Contact Oscar**

You can contact me through this webpage

## Share this article