software

I am doing a match function using dates. Column A has weekly dates with a
Header, quot;Datequot;, 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, quot;01/11/99quot;, #01/11/99quot;.

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

Hi!

Try this:

=MATCH(quot;01/07/05quot;,INDEX(TEXT(A1:A10,quot;mm/dd/yyquot;),,1),0)

Biff

quot;Davidquot; gt; wrote in message
news
gt;I am doing a match function using dates. Column A has weekly dates with a
gt; Header, quot;Datequot;, going back about 5 years and formated short, mm/dd/yy.
gt; Match
gt; works if I use a cell address with a date in it, it works if I use the
gt; serial
gt; number for the date, but I am having a really hard time trying to put the
gt; date in manually ie as a Text Value. The literals I have tried include
gt; 01/11/99, quot;01/11/99quot;, #01/11/99quot;.
gt;
gt; I have also tried using the DateValue function in an attempt to get the
gt; Serail number of the date, but keep getting #VALUE!, although the serial
gt; number in know by Excel and is apparent whan a cell is selected. The
gt; serial
gt; number is visible as soon as a cell is selected, but the answer does not
gt; translate down.
gt;
gt; In the end I will be trying to take a date, add 365 days to it and do a
gt; match funtion based on this calculated datebut I am having a hard time
gt; just
gt; making the function work well. Then I will trying and put it into code.
gt; Any
gt; help would be appreciated.
gt;
gt;
gt; --
gt; David
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(quot;01/11/99quot;,INDEX(TEXT(A1:A360,quot;mm/dd/yyquot;),,0),0) this formula yields
the correct row, 359.

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

=MATCH(quot;01/10/99quot;,INDEX(TEXT(A1:A360,quot;mm/dd/yyquot;),,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.

--
Davidquot;Biffquot; wrote:

gt; Hi!
gt;
gt; Try this:
gt;
gt; =MATCH(quot;01/07/05quot;,INDEX(TEXT(A1:A10,quot;mm/dd/yyquot;),,1),0)
gt;
gt; Biff
gt;
gt; quot;Davidquot; gt; wrote in message
gt; news
gt; gt;I am doing a match function using dates. Column A has weekly dates with a
gt; gt; Header, quot;Datequot;, going back about 5 years and formated short, mm/dd/yy.
gt; gt; Match
gt; gt; works if I use a cell address with a date in it, it works if I use the
gt; gt; serial
gt; gt; number for the date, but I am having a really hard time trying to put the
gt; gt; date in manually ie as a Text Value. The literals I have tried include
gt; gt; 01/11/99, quot;01/11/99quot;, #01/11/99quot;.
gt; gt;
gt; gt; I have also tried using the DateValue function in an attempt to get the
gt; gt; Serail number of the date, but keep getting #VALUE!, although the serial
gt; gt; number in know by Excel and is apparent whan a cell is selected. The
gt; gt; serial
gt; gt; number is visible as soon as a cell is selected, but the answer does not
gt; gt; translate down.
gt; gt;
gt; gt; In the end I will be trying to take a date, add 365 days to it and do a
gt; gt; match funtion based on this calculated datebut I am having a hard time
gt; gt; just
gt; gt; making the function work well. Then I will trying and put it into code.
gt; gt; Any
gt; gt; help would be appreciated.
gt; gt;
gt; gt;
gt; gt; --
gt; gt; David
gt;
gt;
gt;

If Date is in ascending order:

=MATCH(quot;01/11/99quot; 0,DateRange,1)

Otherwise, replace 1 with 0.

David wrote:
gt; I am doing a match function using dates. Column A has weekly dates with a
gt; Header, quot;Datequot;, going back about 5 years and formated short, mm/dd/yy. Match
gt; works if I use a cell address with a date in it, it works if I use the serial
gt; number for the date, but I am having a really hard time trying to put the
gt; date in manually ie as a Text Value. The literals I have tried include
gt; 01/11/99, quot;01/11/99quot;, #01/11/99quot;.
gt;
gt; I have also tried using the DateValue function in an attempt to get the
gt; Serail number of the date, but keep getting #VALUE!, although the serial
gt; number in know by Excel and is apparent whan a cell is selected. The serial
gt; number is visible as soon as a cell is selected, but the answer does not
gt; translate down.
gt;
gt; In the end I will be trying to take a date, add 365 days to it and do a
gt; match funtion based on this calculated datebut I am having a hard time just
gt; making the function work well. Then I will trying and put it into code. Any
gt; help would be appreciated.
gt;
gt;

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(quot;01/11/99quot;,INDEX(TEXT(A1:A360,quot;mm/dd/yyquot;),,0),0)-1

--
Regards,

Peo Sjoblom

(No private emails please)quot;Davidquot; gt; wrote in message
...
gt; Hi Biff,
gt;
gt; This works, I get the correct row as the answer, assuming the date exists
gt; in
gt; the table. But if I experiment, change the date to a date that does not
gt; exist
gt; and the last index parameter to -1, i begin to get erroneous answers.
gt;
gt; =MATCH(quot;01/11/99quot;,INDEX(TEXT(A1:A360,quot;mm/dd/yyquot;),,0),0) this formula
gt; yields
gt; the correct row, 359.
gt;
gt; =MATCH(quot;01/10/99quot;,INDEX(TEXT(A1:A360,quot;mm/dd/yyquot;),,0),0) this formula
gt; yields
gt; #N/A, which is expected, if i read the formulas correctly.
gt;
gt; =MATCH(quot;01/10/99quot;,INDEX(TEXT(A1:A360,quot;mm/dd/yyquot;),,0),-1) this formula
gt; yields
gt; 46, which is not what was expected at all. The date changed by one day
gt; only
gt; and I expected with the -1 parameter change would get the date just below
gt; that date (the closest date, but below it.) The date that found is
gt; 01/14/05.
gt;
gt; In the end i should be able to take todays date, subtract 365 days and
gt; find
gt; the closest date that either matches it exactly or is just a little less
gt; than
gt; that date. It is very unlikely that it would ever match exactly.
gt;
gt; --
gt; David
gt;
gt;
gt; quot;Biffquot; wrote:
gt;
gt;gt; Hi!
gt;gt;
gt;gt; Try this:
gt;gt;
gt;gt; =MATCH(quot;01/07/05quot;,INDEX(TEXT(A1:A10,quot;mm/dd/yyquot;),,1),0)
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;Davidquot; gt; wrote in message
gt;gt; news
gt;gt; gt;I am doing a match function using dates. Column A has weekly dates with
gt;gt; gt;a
gt;gt; gt; Header, quot;Datequot;, going back about 5 years and formated short, mm/dd/yy.
gt;gt; gt; Match
gt;gt; gt; works if I use a cell address with a date in it, it works if I use the
gt;gt; gt; serial
gt;gt; gt; number for the date, but I am having a really hard time trying to put
gt;gt; gt; the
gt;gt; gt; date in manually ie as a Text Value. The literals I have tried include
gt;gt; gt; 01/11/99, quot;01/11/99quot;, #01/11/99quot;.
gt;gt; gt;
gt;gt; gt; I have also tried using the DateValue function in an attempt to get the
gt;gt; gt; Serail number of the date, but keep getting #VALUE!, although the
gt;gt; gt; serial
gt;gt; gt; number in know by Excel and is apparent whan a cell is selected. The
gt;gt; gt; serial
gt;gt; gt; number is visible as soon as a cell is selected, but the answer does
gt;gt; gt; not
gt;gt; gt; translate down.
gt;gt; gt;
gt;gt; gt; In the end I will be trying to take a date, add 365 days to it and do a
gt;gt; gt; match funtion based on this calculated datebut I am having a hard time
gt;gt; gt; just
gt;gt; gt; making the function work well. Then I will trying and put it into code.
gt;gt; gt; Any
gt;gt; gt; help would be appreciated.
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; --
gt;gt; gt; David
gt;gt;
gt;gt;
gt;gt;I would recommend Aladin's formula. Much shorter. I didn't even think of
doing it that way!

Biff

quot;Peo Sjoblomquot; gt; wrote in message
...
gt; The -1 does not offset the result, it tells excel what match type to use,
gt; you need all number/dates to be sorted in descending order and it will
gt; find the smallest value that is greater than or equal to lookup value, if
gt; you want to offset the result put -1 after the formula but do not change
gt; the match type
gt;
gt; =MATCH(quot;01/11/99quot;,INDEX(TEXT(A1:A360,quot;mm/dd/yyquot;),,0),0)-1
gt;
gt; --
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; (No private emails please)
gt;
gt;
gt; quot;Davidquot; gt; wrote in message
gt; ...
gt;gt; Hi Biff,
gt;gt;
gt;gt; This works, I get the correct row as the answer, assuming the date exists
gt;gt; in
gt;gt; the table. But if I experiment, change the date to a date that does not
gt;gt; exist
gt;gt; and the last index parameter to -1, i begin to get erroneous answers.
gt;gt;
gt;gt; =MATCH(quot;01/11/99quot;,INDEX(TEXT(A1:A360,quot;mm/dd/yyquot;),,0),0) this formula
gt;gt; yields
gt;gt; the correct row, 359.
gt;gt;
gt;gt; =MATCH(quot;01/10/99quot;,INDEX(TEXT(A1:A360,quot;mm/dd/yyquot;),,0),0) this formula
gt;gt; yields
gt;gt; #N/A, which is expected, if i read the formulas correctly.
gt;gt;
gt;gt; =MATCH(quot;01/10/99quot;,INDEX(TEXT(A1:A360,quot;mm/dd/yyquot;),,0),-1) this formula
gt;gt; yields
gt;gt; 46, which is not what was expected at all. The date changed by one day
gt;gt; only
gt;gt; and I expected with the -1 parameter change would get the date just below
gt;gt; that date (the closest date, but below it.) The date that found is
gt;gt; 01/14/05.
gt;gt;
gt;gt; In the end i should be able to take todays date, subtract 365 days and
gt;gt; find
gt;gt; the closest date that either matches it exactly or is just a little less
gt;gt; than
gt;gt; that date. It is very unlikely that it would ever match exactly.
gt;gt;
gt;gt; --
gt;gt; David
gt;gt;
gt;gt;
gt;gt; quot;Biffquot; wrote:
gt;gt;
gt;gt;gt; Hi!
gt;gt;gt;
gt;gt;gt; Try this:
gt;gt;gt;
gt;gt;gt; =MATCH(quot;01/07/05quot;,INDEX(TEXT(A1:A10,quot;mm/dd/yyquot;),,1),0)
gt;gt;gt;
gt;gt;gt; Biff
gt;gt;gt;
gt;gt;gt; quot;Davidquot; gt; wrote in message
gt;gt;gt; news
gt;gt;gt; gt;I am doing a match function using dates. Column A has weekly dates with
gt;gt;gt; gt;a
gt;gt;gt; gt; Header, quot;Datequot;, going back about 5 years and formated short, mm/dd/yy.
gt;gt;gt; gt; Match
gt;gt;gt; gt; works if I use a cell address with a date in it, it works if I use the
gt;gt;gt; gt; serial
gt;gt;gt; gt; number for the date, but I am having a really hard time trying to put
gt;gt;gt; gt; the
gt;gt;gt; gt; date in manually ie as a Text Value. The literals I have tried include
gt;gt;gt; gt; 01/11/99, quot;01/11/99quot;, #01/11/99quot;.
gt;gt;gt; gt;
gt;gt;gt; gt; I have also tried using the DateValue function in an attempt to get
gt;gt;gt; gt; the
gt;gt;gt; gt; Serail number of the date, but keep getting #VALUE!, although the
gt;gt;gt; gt; serial
gt;gt;gt; gt; number in know by Excel and is apparent whan a cell is selected. The
gt;gt;gt; gt; serial
gt;gt;gt; gt; number is visible as soon as a cell is selected, but the answer does
gt;gt;gt; gt; not
gt;gt;gt; gt; translate down.
gt;gt;gt; gt;
gt;gt;gt; gt; In the end I will be trying to take a date, add 365 days to it and do
gt;gt;gt; gt; a
gt;gt;gt; gt; match funtion based on this calculated datebut I am having a hard time
gt;gt;gt; gt; just
gt;gt;gt; gt; making the function work well. Then I will trying and put it into
gt;gt;gt; gt; code.
gt;gt;gt; gt; Any
gt;gt;gt; gt; help would be appreciated.
gt;gt;gt; gt;
gt;gt;gt; gt;
gt;gt;gt; gt; --
gt;gt;gt; gt; David
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;
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 quot; 0quot; 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.
--
Davidquot;Aladin Akyurekquot; wrote:

gt; If Date is in ascending order:
gt;
gt; =MATCH(quot;01/11/99quot; 0,DateRange,1)
gt;
gt; Otherwise, replace 1 with 0.
gt;
gt; David wrote:
gt; gt; I am doing a match function using dates. Column A has weekly dates with a
gt; gt; Header, quot;Datequot;, going back about 5 years and formated short, mm/dd/yy. Match
gt; gt; works if I use a cell address with a date in it, it works if I use the serial
gt; gt; number for the date, but I am having a really hard time trying to put the
gt; gt; date in manually ie as a Text Value. The literals I have tried include
gt; gt; 01/11/99, quot;01/11/99quot;, #01/11/99quot;.
gt; gt;
gt; gt; I have also tried using the DateValue function in an attempt to get the
gt; gt; Serail number of the date, but keep getting #VALUE!, although the serial
gt; gt; number in know by Excel and is apparent whan a cell is selected. The serial
gt; gt; number is visible as soon as a cell is selected, but the answer does not
gt; gt; translate down.
gt; gt;
gt; gt; In the end I will be trying to take a date, add 365 days to it and do a
gt; gt; match funtion based on this calculated datebut I am having a hard time just
gt; gt; making the function work well. Then I will trying and put it into code. Any
gt; gt; help would be appreciated.
gt; gt;
gt; gt;
gt;