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