Does date fall between two ranges?

[MR]

Hi,

Having a bit of difficulty constructing a formula to work out the
following:

We have a table of dates and a 'category' roughly as follows:

Start Date # End Date # Category
Date 1 # Date 2 # A
Date 3 # Date 4 # A
Date 5 # Date 6 # B
Date 7 # Date 8 # B

And so on. These dates will be in time order, so that Date 1 is the
earliest, then Date 2, then Date 3, etc.

Then we have a column of dates which can vary, so:

Sample Date
Date A
Date B
Date C

We would expect these also to be in time order, but may not always be.

What we are looking for is a formula to take the SAMPLE DATE, locate
which of the START DATE and END DATE ranges this falls in between, and
then return the vlaue from the CATEGORY column.

If the SAMPLE DATE does not fall between any of the given date ranges
it would have to return a blank value such as 'None' or whatever.

I have had a google and the closest thing I could find was:

http://tinyurl.co.uk/ebg3

But I tried altering this and could not get it to work.

Thanks for any help!

MR

 

[David McRitchie]

Lookup AND Worksheet Function in HELP.

=IF(AND(A1>= Date(2004,01,02),A1<=Date(2004,03,15)),"In Range","Forget About It")

 

[Bernie Deitrick]

MR,

With your sample table in cells A1:C5, and your target dates in cell
A8:A10, enter this formula in cell B8 and copy to B9 and B10: (Remove
any extra returns from the line wrapping that will be thrown in by the
mail programs)

=IF(SUMPRODUCT((A8>=$A$2:$A$5)*(A8<=$B$2:$B$5)*ROW($A$2:$A$5))=0,"None
",INDIRECT(ADDRESS(SUMPRODUCT((A8>=$A$2:$A$5)*(A8<=$B$2:$B$5)*ROW($A$2
:$A$5)),3)))

HTH,
Bernie
MS Excel MVP

 

[Ron Rosenfeld]

Hi,

Having a bit of difficulty constructing a formula to work out the
following:

We have a table of dates and a 'category' roughly as follows:

Start Date # End Date # Category
Date 1 # Date 2 # A
Date 3 # Date 4 # A
Date 5 # Date 6 # B
Date 7 # Date 8 # B

And so on. These dates will be in time order, so that Date 1 is the
earliest, then Date 2, then Date 3, etc.

Then we have a column of dates which can vary, so:

Sample Date
Date A
Date B
Date C

We would expect these also to be in time order, but may not always be.

What we are looking for is a formula to take the SAMPLE DATE, locate
which of the START DATE and END DATE ranges this falls in between, and
then return the vlaue from the CATEGORY column.

If the SAMPLE DATE does not fall between any of the given date ranges
it would have to return a blank value such as 'None' or whatever.

I have had a google and the closest thing I could find was:

Assuming the "Table" is in three columns, then this should work:

=IF(ISNA(VLOOKUP(VLOOKUP(Sample_Date,Table,1),Table,2)),"none",
IF(Sample_Date<=VLOOKUP(VLOOKUP(Sample_Date,Table,1),Table,2),
VLOOKUP(VLOOKUP(Sample_Date,Table,1),Table,3,),"none"))


--ron

 

[Daniel.M]

Hi,

Very well explained problem.

Beg: The Start dates (A2:Ax)
Last: The End dates (B2:Bx)
F2: The searched date
The categories are in column C (or else change the 1st arg of INDEX)

The following ARRAY formula (Ctrl-Shift-Enter):

=IF(MAX((F2>=Beg)*(F2<=Last))=0,"None",INDEX(C:C,
MAX((F2>=Beg)*(F2<=Last)*ROW(Beg))))

Regards,

Daniel M.