Does this date range contain ??

[blossomthe2nd]

Can I ask 1 more thing re dates,

I have the below table
B C
<TABLE style="WIDTH: 106pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=142 border=0 x:str><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" span=2 width=71><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; WIDTH: 53pt; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" align=right width=71 height=17 x:num="40087">01/10/2009</TD><TD class=xl22 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; WIDTH: 53pt; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" align=right width=71 x:num="40118">01/11/2009</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" align=right height=17 x:num="37987">01/01/2004</TD><TD class=xl22 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" align=right x:num="38018">01/02/2004</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" align=right height=17 x:num="38749">01/02/2006</TD><TD class=xl22 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" align=right x:num="39873">01/03/2009</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" align=right height=17 x:num="37257">01/01/2002</TD><TD class=xl22 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" align=right x:num="37288">01/02/2002</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" align=right height=17 x:num="39448">01/01/2008</TD><TD class=xl22 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" align=right x:num="40575">01/02/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" align=right height=17 x:num="40545">02/01/2011</TD><TD class=xl22 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" align=right x:num="40576">02/02/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" align=right height=17 x:num="40546">03/01/2011</TD><TD class=xl22 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" align=right x:num="40577">03/02/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" align=right height=17 x:num="40547">04/01/2011</TD><TD class=xl22 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" align=right x:num="40578">04/02/2011</TD></TR></TBODY></TABLE>

I wish to write a formula in Column D thats checks if any of the dates in column N falls between the 2 dates

N

<TABLE style="WIDTH: 53pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=71 border=0 x:str><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; WIDTH: 53pt; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" align=right width=71 height=17 x:num="39448">01/01/2008</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" align=right height=17 x:num="38018">01/02/2004</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" align=right height=17 x:num="39508">01/03/2008</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" align=right height=17 x:num="39539">01/04/2008</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" align=right height=17 x:num="39569">01/05/2008</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" align=right height=17 x:num="39600">01/06/2008</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" align=right height=17 x:num="39630">01/07/2008</TD></TR></TBODY></TABLE>

Can you help me with this ? <!-- / message -->

 

[Sektor]

UDF. First argument d - date to compare, d1 - start date, d2 - end date.

Function IsDateBetween(d As Date, d1 As Date, d2 As Date) As Boolean
    Application.Volatile
    IsDateBetween = (d > d1) And (d < d2)
End Function

 

[blossomthe2nd]

Thanks Sektor

I'm kinda scared of VBA

I think this works

=SUMPRODUCT(($F$1:$F$10000>=B6)*($F$1:$F$10000<=C6))

But now I am getting results that is Numerical like , 32, 3 0 etc. Whihc is fine,

Do you know the easiest way to then find out whihc columns within F1:F100000 the matches are ?

Thanks

Anne

 

[Sektor]

Try this. Cell format must be General: AND(N1>B1,N1<C1)*1

 

[Jared_8878]

=IF(SUMPRODUCT(--(K2>A1:A26),
--(K2
<
B1:B26)
)>0,"Yes","")


ofcourse all as one, but when posting it was removing part of the formula, so I had to break it out in lines

 

[blossomthe2nd]

Thanks Guys

Sumproduct working perfectly

 

[JD_GFI]

Do you mean, for example, only check if cell N3 falls between the dates in A3 and B3?

If so then the formula should be:

=IF(SUMPRODUCT((N1>=A1)*(N1<=B1)),"Yes","No")

I think