PDA

View Full Version : [SOLVED] Date overlap



jumpyjim
03-07-2014, 03:42 PM
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
03-07-2014, 08:19 PM
Try this
D1=AND(B1>=A2,B1<=B2)

jumpyjim
03-08-2014, 04:49 AM
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
03-08-2014, 08:46 AM
Isn't that what the solution given does?

jumpyjim
03-08-2014, 09:24 AM
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
03-08-2014, 09:41 AM
Then maybe

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

Paul_Hossler
03-08-2014, 10:56 AM
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
03-08-2014, 11:25 AM
Sure

Bob Phillips
03-08-2014, 12:27 PM
Use

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

jumpyjim
03-08-2014, 01:46 PM
Thank you xld :)