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

### Category: Overlapping

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 […]Comments(31) Filed in category: Excel, Overlapping

Find overlapping date ranges in excel

Table of Contents Find overlapping date ranges Find overlapping date ranges with criterion Find overlapping date ranges Formula in cell […]Comments(24) Filed in category: Excel, Overlapping

Highlight duplicate values and overlapping dates in excel

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]Comments(23) Filed in category: Excel, Overlapping

Visualize date ranges in a calendar

Here is a picture of a simple calendar. I have used conditional formatting to: highlight date ranges (green) highlight possible […]Comments(10) Filed in category: Calendar, Excel, Overlapping

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, […]Comments(8) Filed in category: Dates, Excel, Overlapping

Comments(7) Filed in category: Excel, Overlapping

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 […]Comments(7) Filed in category: Excel, Overlapping

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 […]Comments(6) Filed in category: Excel, Overlapping

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 […]Comments(4) Filed in category: Overlapping

Count overlapping dates in excel

Table of Contents Count all overlapping days in any number of date ranges Count overlapping days in a date range […]Comments(4) Filed in category: Excel, Overlapping