Date overlap

[jumpyjim]

Hi all, my first forum post and a novice so be gentle

In Excel 2010/2013, I want a TRUE/FALSE result should the B1 date fall between the date range for A2 (start date) to B2 (end date). If possible but as separate formulas, also whether B2 falls into A3 to B3 date range and B3 to A4 and B4 and so on. All cells are formatted to date and the date data in cells A1 to B6 are results of IF statements.

At its simplest form, the cells are:


Start date End date Date overlap

A1 01/01/2002 B1 01/07/2003 D1 FALSE
A2 04/08/2003 B2 04/01/2008 D2 TRUE
A3 11/01/2003 B3 15/01/2009 D3 TRUE
A4 21/01/2009 B4 01/02/2011 D4 FALSE
A5 15/05/2009 B5 18/03/2011 D5 TRUE
A6 19/03/2012 B6 20/06/2013 D6 FALSE

Your help is much appreciated.


in D1 I have {=$B1>=$A2:$B2}

[anandbohra]

Try this
D1=AND(B1>=A2,B1<=B2)

[jumpyjim]

Thank you for your quick response. It works! If you were to expand the above example a little, if I wanted to compare an entire date range to another date range, such as return TRUE if the dates in start date cell A2 and end date cell B2 overlap the date periods held in A3 and B3?

[Bob Phillips]

Isn't that what the solution given does?

[jumpyjim]

As I had first asked, the solution takes a single date and checks if that one date falls between a start date and end date range it returns TRUE, whereas I'd also like to take one date range with a start and stop date and see if that date range overlaps on another date range also with a start and end date.

[Bob Phillips]

Then maybe

=AND(B3>=A2,A3<=B2)

[Paul_Hossler]

As I had first asked, the solution takes a single date and checks if that one date falls between a start date and end date range it returns TRUE, whereas I'd also like to take one date range with a start and stop date and see if that date range overlaps on another date range also with a start and end date.

Got an example?

Paul

[jumpyjim]

Sure

[Bob Phillips]

Use

=IF(AND(B1>=A2,A1<=B2),"Date overlaps","No overlap")

[jumpyjim]

Thank you xld