Identifying if a date range is between a date range

[DaveyK]

Hi all,

I've been scratching my head for some time now and the solution is probably easier than I thought, however I need to find a solution that will identify if the range between the start date (Column E) and the 9mth date (Column F) falls between the dates specified in column H (See example below)

Start Date 9mth Date 01/08/2013 - 31/10/2013

17/06/2013 18/03/2014 To state yes or no
19/05/2014 17/02/2015 NO
20/08/2013 21/05/2014 YES
Etc...

I'm on Excel 2010 and I hope I have explained this well enough.

Thanks in advance

 

[Special-K99]

in C2
=IF(AND(A$1>=A2,B$1<=B2),"Yes","No)
and copy down the column

where A1 is your start date and B1 is your 9mth Date (Put the dates in separate cells)
and your date columns start at A2 and B2

 

[oldbrewer]

	start	end	within range			specified date range	13/01/2014	22/03/2014
job1	02/01/2014	14/02/2014	no
job2	07/01/2014	17/02/2014	no
job3	12/01/2014	20/02/2014	no
job4	17/01/2014	23/02/2014	yes
job5	22/01/2014	26/02/2014	yes
job6	27/01/2014	01/03/2014	yes
job7	01/02/2014	04/03/2014	yes
job8	06/02/2014	07/03/2014	yes
job9	11/02/2014	10/03/2014	yes
job10	16/02/2014	13/03/2014	yes
job11	21/02/2014	16/03/2014	yes
job12	26/02/2014	19/03/2014	yes
job13	03/03/2014	22/03/2014	yes
job14	08/03/2014	25/03/2014	no
job15	13/03/2014	28/03/2014	no
job16	18/03/2014	31/03/2014	no
job17	23/03/2014	03/04/2014	no
job18	28/03/2014	06/04/2014	no
job19	02/04/2014	09/04/2014	no
job20	07/04/2014	12/04/2014	no
	formula in D2
	=IF(B2>=$I$1,IF(C2<=$J$1,"yes","no"),"no")

<colgroup><col><col span="2"><col><col span="4"><col span="2"><col></colgroup><tbody>
</tbody>

 

[DaveyK]

Thanks for the prompt reply Special-K99, I knew my example wouldn't explain it correctly!...my apologies.

I'll try again...

Column A
Start Date
(Cell - A2) 17/06/2013

Column B
End Date
(Cell - B2) 18/03/2014

Column C
Do the above dates fall between 01/08/2013 & 31/10/2013
(Cell - C2) - Yes or No

Hope this is better.

 

[Special-K99]

Try

=IF(AND(DATEVALUE("01/08/2013")>=A2,DATEVALUE("31/10/2013")<=B2),"Yes","No)

 

[barry houdini]

Hello DaveyK, welcome to MrExcel

What constitutes "yes"? Does there just have to be some overlap between the date ranges?

If so then try

=IF(OR(A2>DATE(2013,10,31),B2< DATE(2013,8,1)),"No","Yes")

 

[DaveyK]

I think we've cracked it people...many thanks for your replies and welcome.

Now I know where to come in future.