# Identifying if a date range is between a date range

#### DaveyK

##### New Member
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.

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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

 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>

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.

Try

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

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")

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

Now I know where to come in future.

Replies
6
Views
289
Replies
1
Views
444
Replies
2
Views
2K
Replies
2
Views
587
Replies
6
Views
207

1,203,515
Messages
6,055,845
Members
444,828
Latest member
StaffordStag

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back