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
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
Functions in this article:
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
Returns the smallest number in a set of values. Ignores logical values and text
ROW(reference) returns the rownumber of a reference
Adds all the numbers in a range of cells
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.
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