formula to fiind where a value falls between 2 of a series of oth.

[Neostand]

I have a column with a progressive series of dates in each cell. I.e.
01/01/04, 02/02/04,03/03/04,05/05/04 etc. I have a cell with a date in it
such as 04/03/04. I need a formula to find out what 2 dates in the series
that the date 04/03/04 falls between or is equal to.

 

[Frank Kabel]

Hi
lets say your dates are in column A and your date to look for is is B1.
Try the following formulas:
1. Smaller or equal (if column A is sorted):
=VLOOKUP(B1,A1:A100,1,TRUE)

2. Larger or Equal: use the following array formula (entered with
CTRL+SHIFT+ENTER):
=INDEX(A1:A100,MATCH(TRUE,A1:A100>=A1,0))

 

[Neostand]

Thanks Frank,
I think we may be looking in opposite directions.
I think in your example i will find out which in the series in column A is

= or <= the one in column B. I would like to find out where the date in

column B falls between which 2 dates in column A which are progessive in
order.

 

[Frank Kabel]

Hi
do you mean you want the row numbers from column A? If yes use:
1. Smaller or equal (if column A is sorted):
=MATCH(B1,A1:A100,TRUE)

2. Larger or Equal: use the following array formula (entered with
CTRL+SHIFT+ENTER):
=MATCH(TRUE,A1:A100>=A1,0)

 

[Myrna Larson]

Are you saying that column A contains more than one date? if so, you've got a
problem <g>.

To accomplish this easily, you need to have just one date in a cell. If the
maximum is, say, 4 dates in a cell, use Data/Text to columns to separate them
(delimiters are comma and space). Then, since you say they are in
chronological order, you can use a simple HLOOKUP formula:

=HLOOKUP(E2,A22,1,TRUE)

over on the right.

Or you need to make one row for each date that is now in column A, i.e. 4
dates, 4 rows.

If you can't split the data as I describe, you probably need a VBA function.

 

[Neostand]

Thanks Again Frank
That was close. The array formula gave me the number of times that the date
in column B was greater then all of the dates in column A. However I would
like it to only count if the date falls between 2 specific dates. A
combination of the 2 options that you offer

 

[Frank Kabel]

Hi
now I'm confused.
Give an example of your expected result