## Count overlapping dates

*Article updated on January 14, 2018*

**Table of Contents**

### Count all overlapping days in any number of date ranges

I have created some random overlapping date ranges in E17:E21 and I17:I21. I also included a calender so you can easily count and verify overlapping dates. Green cells are dates found once within the date ranges. Red cells are overlapping dates. Dates found within at least two or more date ranges.

**Array formula in cell F23:**

**How to create an array formula**

- Select cell F23
- Click in formula bar
- Copy and paste above formula to formula bar
- Press and hold CTRL + SHIFT
- Press ENTER
- Release all keys

**Named range**

Start ($E$17:$E$21)

End ($I$17:$I$21)

What is a named range?

**Download excel sample file for this tutorial. **

Count overlapping dates in excel.xls

(Excel 97-2003 Workbook *.xls)

### Count overlapping days in a date range

**Array formula in cell D2:**

Here is a picture of all the date ranges:

Jeff: Two dates are overlapping, 7 and 8

Thomas: Two dates are overlapping, 7 and 8

Tim: Two dates are overlapping, 8 and 9

Shaun: One date is overlapping, 9.

Simon: No dates are overlapping

**Download excel file**

Count-overlapping-dates-in-excel2.xls

**Functions in this article:**

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

**MIN(**number1,[number2]**)**

Returns the smallest number in a set of values. Ignores logical values and text

**ROW(**reference**)** returns the rownumber of a reference

**SUM(**number1,[number2],**)
**Adds all the numbers in a range of cells

**FREQUENCY**(data_array, bins_array)*
*Calculates how often values occur within a range of values and then returns a vertical array of numbers having one more element than Bins_array

*.*

**OFFSET(**reference,rows,cols, [height],[width]**)**

Returns a reference to a range that is a given number of rows and columns from a given reference

**COLUMN(**reference**) **returns the column number of a reference

Count unique distinct values within same week, month or year

Introduction What are unique distinct values? Unique distinct values are all values but duplicates are merged into one value. Count […]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 […]Count how many times a string exists in a cell range (case insensitive)

Question: How do I count how many times a word exists in a range of cells? It does not have […]### 5 Responses to “Count overlapping dates”

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

Is there a formula that can count blocks

For eg in your picture if the green blocks had the letter G and the Red blocks had the letter R and I had to return 4 as the answer -3G + 1R

Is this possible through a formula

Interesting question!

I will look into this as soon as possible!

Thank you for commenting!

I have a series of support tickets that start at a date time in the format mm/dd/yyyy HH:MM and then close at a date and time in the same format. The data in the format is all in a single cell. What I need to understand is how many of these overlap and are tickets being supported at the same time. Some tickets run for days. Have anything for that. Trying to determine how many support tickets are going at the same time so that I can understand how many support people I need on the job at any given time to support the load. Thank you.

Greg,

Can you upload an example workbook?

How would I modify this formula to exclude any days that land on sunday? something like a

`if(weekday()={2,3,4,5,6,7})`

Thanks!