Find overlapping date ranges in excel

Dashboard reporting with excel This e-book teaches you how to create your own Excel dashboard reports, starting from scratch. Learn how to create mini-charts, how to use Excel's Camera tool, how to set up Excel databases, and a lot more.

Excel Worksheet-Formula Add-Ins allows worksheet formulas to change chart settings. For the first time ever, your formulas can create traffic-light charts, highlight chart elements, assign number formats, and much more.

You are here: Home >> Excel >> Dates >> Find overlapping date ranges in excel

Formula in cell E6:

=SUMPRODUCT((B6< =$C$6:$C$12)*(C6>=$B$6:$B$12))>1

Copy cell E6 and paste it down as far as needed.

Download excel sample file for this tutorial.
overlapping dates.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays

Oscar Says:
June 1st, 2010 at 8:37 pm

Chandoo has posted a much shorter better formula: http://chandoo.org/wp/2010/06/01/date-overlap-formulas/
Peter Says:
June 3rd, 2010 at 6:07 am

Oscar, could you explain the COUNT($B$6:$B$12)-1 bit in the formula...
Oscar Says:
June 3rd, 2010 at 6:59 am

Peter,
Here is a shorter formula: =SUMPRODUCT((C6< =$D$6:$D$12)*(D6>=$C$6:$C$12)*(ROW(C6)<>ROW($C$6:$C$12)))>0. Copy cell and paste it down as far as needed.
The formula returns TRUE if overlapping.
Oscar Says:
June 3rd, 2010 at 10:08 pm

Peter,
=SUMPRODUCT((C6< =$D$6:$D$12)*(D6>=$C$6:$C$12))>1 is shorter.
Copy cell and paste it down as far as needed.
The formula returns TRUE if overlapping.
Peter Says:
June 6th, 2010 at 5:27 am

Thanks Oscar...... appreciate your prompt feedback

Related blog posts