Does date fall between two ranges?

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

Lookup AND Worksheet Function in HELP.

=IF(AND(A1= Date(2004,01,02),A1=Date(2004,03,15)),"In Range","Forget About It")
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"MR" wrote in message om...
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

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

"MR" wrote in message
om...
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

On 14 Jan 2004 04:28:02 -0800, (MR) wrote:

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),Tabl e,2)),"none",
IF(Sample_Date=VLOOKUP(VLOOKUP(Sample_Date,Table, 1),Table,2),
VLOOKUP(VLOOKUP(Sample_Date,Table,1),Table,3,),"no ne"))


--ron

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.

"MR" wrote in message
om...
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