I would recommend Aladin's formula. Much shorter. I didn't even think of
doing it that way!

Biff

"Peo Sjoblom" wrote in message
...
The -1 does not offset the result, it tells excel what match type to use,
you need all number/dates to be sorted in descending order and it will
find the smallest value that is greater than or equal to lookup value, if
you want to offset the result put -1 after the formula but do not change
the match type

=MATCH("01/11/99",INDEX(TEXT(A1:A360,"mm/dd/yy"),,0),0)-1

--
Regards,

Peo Sjoblom

(No private emails please)


"David" wrote in message
...
Hi Biff,

This works, I get the correct row as the answer, assuming the date exists
in
the table. But if I experiment, change the date to a date that does not
exist
and the last index parameter to -1, i begin to get erroneous answers.

=MATCH("01/11/99",INDEX(TEXT(A1:A360,"mm/dd/yy"),,0),0) this formula
yields
the correct row, 359.

=MATCH("01/10/99",INDEX(TEXT(A1:A360,"mm/dd/yy"),,0),0) this formula
yields
#N/A, which is expected, if i read the formulas correctly.

=MATCH("01/10/99",INDEX(TEXT(A1:A360,"mm/dd/yy"),,0),-1) this formula
yields
46, which is not what was expected at all. The date changed by one day
only
and I expected with the -1 parameter change would get the date just below
that date (the closest date, but below it.) The date that found is
01/14/05.

In the end i should be able to take todays date, subtract 365 days and
find
the closest date that either matches it exactly or is just a little less
than
that date. It is very unlikely that it would ever match exactly.

--
David


"Biff" wrote:

Hi!

Try this:

=MATCH("01/07/05",INDEX(TEXT(A1:A10,"mm/dd/yy"),,1),0)

Biff

"David" wrote in message
...
I am doing a match function using dates. Column A has weekly dates with
a
Header, "Date", going back about 5 years and formated short, mm/dd/yy.
Match
works if I use a cell address with a date in it, it works if I use the
serial
number for the date, but I am having a really hard time trying to put
the
date in manually ie as a Text Value. The literals I have tried include
01/11/99, "01/11/99", #01/11/99".

I have also tried using the DateValue function in an attempt to get
the
Serail number of the date, but keep getting #VALUE!, although the
serial
number in know by Excel and is apparent whan a cell is selected. The
serial
number is visible as soon as a cell is selected, but the answer does
not
translate down.

In the end I will be trying to take a date, add 365 days to it and do
a
match funtion based on this calculated datebut I am having a hard time
just
making the function work well. Then I will trying and put it into
code.
Any
help would be appreciated.


--
David