Find the position of a date

  • Thread starter Thread starter TechTrend
  • Start date Start date
T

TechTrend

Hi,

I have in column A descending dates as:
Mar 1/11
Feb 1/11
Jan 3/11
Dec 1/10
Nov 1/10
Oct 1/10
Sep 1/10
Aug 2/10
Jul 1/10
Jun 1/10
May 3/10
Apr 1/10
Mar 1/10
Feb 1/10
Jan 4/10
Dec 1/09
Nov 2/09

I need a formula, that given a month (as number, 12 for example in
cell B1) and the year (2009 for example, in cell B2) gives me the
position of the matching date (16).

I think it should be and array formula= Match(......

Thanks
Mika
 
Try this:

=MATCH(DATE(B2,B1,1),A:A,0)

This returns the relative position of the matched item, so I'm
assuming your dates start in A1. Do not put the formula in column A.

Hope this helps.

Pete
 
I have in column A descending dates as:
Mar 1/11 [....]
Jan 4/10
Dec 1/09
Nov 2/09 [....]
I need a formula, that given a month (as number, 12
for example in cell B1) and the year (2009 for example,
in cell B2) gives me the position of the matching date (16).
Click to expand...

=MATCH(DATE(B2,B1,1),A:A,0)
Click to expand...

I presume Pete means:

=MATCH(DATE(B2,B1,1),A:A,-1)

Note that not all dates in column A are on day 1. The -1 MATCH option
finds the smallest date greater than equal to month/1/year.

That assumes that column has try numeric dates that are formatted as
Custom "mmm d/yy" without quotes.
 
I have in column A  descending dates as:
Mar 1/11 [....]
Jan 4/10
Dec 1/09
Nov 2/09 [....]
I need a formula, that given a month (as number, 12
for example in cell B1) and the year (2009 for example,
in cell B2) gives me the position of the matching date (16).
Click to expand...

=MATCH(DATE(B2,B1,1),A:A,0)
Click to expand...

I presume Pete means:

=MATCH(DATE(B2,B1,1),A:A,-1)

Note that not all dates in column A are on day 1.  The -1 MATCH option
finds the smallest date greater than equal to month/1/year.

That assumes that column has try numeric dates that are formatted as
Custom "mmm d/yy" without quotes.
Click to expand...

Thanks Pete and Joeu

Indeed had to use -1 instead of 0.

Cheers
 
I have in column A  descending dates as:
Mar 1/11 [....]
Jan 4/10
Dec 1/09
Nov 2/09 [....]
I need a formula, that given a month (as number, 12
for example in cell B1) and the year (2009 for example,
in cell B2) gives me the position of the matching date (16).
Click to expand...
Click to expand...
I presume Pete means:

Note that not all dates in column A are on day 1.  The -1 MATCH option
finds the smallest date greater than equal to month/1/year.
Click to expand...
That assumes that column has try numeric dates that are formatted as
Custom "mmm d/yy" without quotes.
Click to expand...

Thanks Pete and Joeu

Indeed had to use -1 instead of 0.

Cheers
Click to expand...

The formula =MATCH(DATE(B2,B1,1),A1:A100,-1) works very well except
when the date (date(b2,b1,1) does not exist in the range a1:a100. In
that case it gives me the position of the last row with data (A100).

Is there a way to modify the formula to get in those cases #N/A or a
text "Don't exist" ??

Cheers
Mika
 
The formula =MATCH(DATE(B2,B1,1),A1:A100,-1)  works very
well except when the date (date(b2,b1,1) does not exist
in the range a1:a100. In that case it gives me the position
of the last row with data (A100).
Click to expand...

No. It gives the position of the earliest date greater than or equal
to DATE(B2,B1,1). That will be the last position only if
DATE(B2,B1,1) is earlier than the last date in the range. It will be
#N/A if DATE(B2,B1,1) is later (more recent) than the first date in
the range.
Is there a way to modify the formula to get in those cases
#N/A or a text "Don't exist" ??
Click to expand...

You question is unclear.

If you simply want "doesn't exist" if the month/year in B1:B2 is
earlier than the last month/year represented in A1:A100, then:

=IF(DATE(B2,B1,1)<A100-DAY(A100), "doesn't exist",
MATCH(DATE(B2,B1,1),A1:A100,-1))

If you also want "doesn't exist" if B1:B2 is later (more recent) than
the first month/year represented in A1:A100, then:

=IF(OR(DATE(B2,B1,1)<A100-DAY(A100),DATE(B2,B1,0)>A1-DAY(A1)),
"doesn't exist", MATCH(DATE(B2,B1,1),A1:A100,-1))

or for XL2007 and later:

=IF(DATE(B2,B1,1)<A100-DAY(A100), "doesn't exist",
IFERROR(MATCH(DATE(B2,B1,1),A1:A100,-1), "doesn't exist"))

However, if you also want "doesn't exist" there are month/years not
represented in the middle of A1:A100, then:

=IF(SUMPRODUCT(--(DATE(B2,B1,0)=A1:A100-DAY(A1:A100))),
MATCH(DATE(B2,B1,1),A1:A100,-1), "doesn't exist")
 
Perfect! , Thanks again

This is what I was looking for:

"If you also want "doesn't exist" if B1:B2 is later (more recent) than
the first month/year represented in A1:A100, then:

=IF(OR(DATE(B2,B1,1)<A100-DAY(A100),DATE(B2,B1,0)>A1-DAY(A1)),
"doesn't exist", MATCH(DATE(B2,B1,1),A1:A100,-1))


Cheers
 
Back
Top