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:

=SUM(IF(FREQUENCY(IF((MIN(Start)+COLUMN(OFFSET($A$1, , 0, , MAX(End)-MIN(Start)+1))-1<=End)+(MIN(Start)+COLUMN(OFFSET($A$1, , 0, , MAX(End)-MIN(Start)+1))-1>=Start)>1, MIN(Start)+COLUMN(OFFSET($A$1, , 0, , MAX(End)-MIN(Start)+1))-1, ""), MIN(Start)+COLUMN(OFFSET($A$1, , 0, , MAX(End)-MIN(Start)+1))-1)>1, 1, 0))

How to create an array formula

  1. Select cell F23
  2. Click in formula bar
  3. Copy and paste above formula to formula bar
  4. Press and hold CTRL + SHIFT
  5. Press ENTER
  6. 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

count overlapping days

Array formula in cell D2:

=SUM(IF(FREQUENCY(IF((B2+COLUMN(OFFSET($A$1,,0,,C2-B2+1))-1<=End)+(B2+COLUMN(OFFSET($A$1,,0,,C2-B2+1))-1>=Start)>1,B2+COLUMN(OFFSET($A$1,,0,,C2-B2+1))-1,""),B2+COLUMN(OFFSET($A$1,,0,,C2-B2+1))-1)>1,1,0))

Here is a picture of all the date ranges:

count overlapping days2

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

Related posts:

Filter overlapping dates from date ranges in excel

Highlight duplicate values and overlapping dates in excel

Find overlapping date ranges in excel

Count date records between two dates in a range in excel

Convert date ranges into dates in excel