Match and Datevalue

  • Thread starter Thread starter tbieri
  • Start date Start date
T

tbieri

Hi,

I am trying to determine what row the first time year 1999,
2000, 2001, etc. appears in a column of dates.

Currently, I am using the following statement
MATCH(DATEVALUE("01/01/2000"),Xvalues,1) where Xvalues is a
defined name for a column containing ascending dates.

I get different results, depending on dates in Xvalues and
the Match Type (-1,0,1). For example if the first date in
the column is 03/03/2000 and Type is 1, then I get #N/A,
which I understand since there is no date less than
01/01/2000. I cannot use Type 0, since it will never be a
exact match. Match Type of -1 returns #N/A.

Is there a better way
Thanks,
tbieri
 
Hi
in your case option'1' is the correct one. To prevent errors you may
use the following:
=IF(ISNA(MATCH(...)),"",MATCH(...))
 
Two options...

1] Create an additional column using...

=YEAR(A2)

supposing that Xvalues is a range in column A. This formula would the be in
B2. Now use:

=MATCH(1999,Brange,0)

This gives you the position of the first occurrence of 1999 within Xvalues.

2]

=MATCH(TRUE,YEAR(Xvalues)=1999,0)

Confirm this formula with control+shift+enter instead of just with enter.
This gives you the position of the first occurrence of 1999 within Xvalues.
 
Back
Top