Hi Aladin,

This seems to be working pretty well, even with dates that are approximate
and I am not sure I understand why it is working, but that maybe ok. By using
the "+0" and changing it to +365, I am getting the date change and
approximation match. Thank you for your help. I still need to do a little
tweeking, but I think I can use this formula with variables and code it too.
Thanks again.
--
David


"Aladin Akyurek" wrote:

If Date is in ascending order:

=MATCH("01/11/99"+0,DateRange,1)

Otherwise, replace 1 with 0.

David wrote:
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.