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

  • Thread starter Thread starter pollywog1961
  • Start date Start date
P

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.
 
one way:

Assume the date to check is in D1:

=IF(SUMPRODUCT(--(D1>=$A$2:$A$9),--(D1<=$B$2:$B$9)),"Yes","No")
 
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.
 
Back
Top