Checking for a date falling between two columns of dates (an array, maybe??)

[pollywog1961]

I have two columns of dates (one titled "Start" the other "Finished").
The dates are unique, and the Start date is always smaller (less than)
the Finish date. I have 8 rows of these dates.

Start Finish
1-Jan-91 15-Jan-91
1-Feb-91 15- Feb-91
1-Mar-91 15-Mar-91

for (currently) 8 rows.

I'd like to create a fomula that will check to see if a date entered
into a cell falls between any one of the ranges of dates created by the
Start and Finish. The response would be a simple Yes or No (logical).

In other words, the response for 12-Jan-91 should be "Yes", and for
22-Feb-91 should be "No".

I've checked out the Excel help on ARRAYS and VLOOKUP etc. and they
don't seem to address the issue of two columns. But, I'll admit, I
might not have dug (digged?) as hard as I should have.

 

[J.E. McGimpsey]

one way:

Assume the date to check is in D1:

=IF(SUMPRODUCT(--(D1>=$A$2:$A$9),--(D1<=$B$2:$B$9)),"Yes","No")

 

[Peo Sjoblom]

seems like this could work

=IF(DAY(A1)<=15,"Yes","NO")

where A1 holds your date

 

[pollywog1961]

Thanks J.E. McGimpsey! Works like a charm!!

 

[b.v.dam]

Hello,


Maybe you mean this (I assumed that you entered the date
in cell B1.

18-jan-91
1-jan-91 true 20-jan-91
15-jan-91 true 15-feb-91
1-mrt-91 untrue 15-mrt-91

Formula is: =IF(AND($B$1>A2;$B$1<C2);"yes";"no")

Put this formula in cell B2 (between dates of the first
ror (beneath cell B1) and copy it down.

Success.

Best regards,

Bert
I hope you mean this.