find date is between date ranges

[sauk304]

Hi.


i would like to find the date between the date ranges.

the date is in cell A1,

the Start Date is in cell B1:B100 and End Date is in C1:C100. how do i use excel if the date within the ranges then it will return a value

example, i want to search date 18.03.2012 between 01.03.2011 to 31.03.2011.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Find Date
[/TD]
[TD]Start Date
[/TD]
[TD]End Date
[/TD]
[TD]Result
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 86"]
<colgroup><col width="86"></colgroup><tbody>[TR]
[TD="class: xl65, width: 86, align: right"]18.03.2011[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 99"]
<colgroup><col width="99"></colgroup><tbody>[TR]
[TD="class: xl65, width: 99, align: right"]01.03.2011[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 74"]
<colgroup><col width="74"></colgroup><tbody>[TR]
[TD="class: xl65, width: 74, align: right"]31.03.2011[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]yes
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 86"]
<colgroup><col width="86"></colgroup><tbody>[TR]
[TD="class: xl65, width: 86, align: right"][TABLE="width: 86"]
<colgroup><col width="86"></colgroup><tbody>[TR]
[TD="class: xl65, width: 86, align: right"]28.01.2011[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 99"]
<colgroup><col width="99"></colgroup><tbody>[TR]
[TD="class: xl65, width: 99, align: right"]01.03.2011[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 74"]
<colgroup><col width="74"></colgroup><tbody>[TR]
[TD="class: xl65, width: 74, align: right"]31.03.2011[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]no
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 86"]
<colgroup><col width="86"></colgroup><tbody>[TR]
[TD="class: xl65, width: 86, align: right"]27.01.2011[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 99"]
<colgroup><col width="99"></colgroup><tbody>[TR]
[TD="class: xl65, width: 99, align: right"]01.03.2011[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 74"]
<colgroup><col width="74"></colgroup><tbody>[TR]
[TD="class: xl65, width: 74, align: right"]31.03.2011[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]no
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 86"]
<colgroup><col width="86"></colgroup><tbody>[TR]
[TD="class: xl65, width: 86, align: right"]22.03.2011[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 99"]
<colgroup><col width="99"></colgroup><tbody>[TR]
[TD="class: xl65, width: 99, align: right"]01.03.2011[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 74"]
<colgroup><col width="74"></colgroup><tbody>[TR]
[TD="class: xl65, width: 74, align: right"]31.03.2011[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]yes
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Robert Mika]

=IF(AND(A2>=B2,A2<=C2),"yes","no")

 

[cyrilbrd]

Also: =IF(A2=MEDIAN(A2:C2),"Yes","No")