Find the position of a date

[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

 

[Pete_UK]

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

 

[joeu2004]

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).

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

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.

 

[TechTrend]

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).

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

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.

Thanks Pete and Joeu

Indeed had to use -1 instead of 0.

Cheers

 

[JIL]

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).

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.

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

Thanks Pete and Joeu

Indeed had to use -1 instead of 0.

Cheers

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

 

[joeu2004]

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).

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" ??

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")

 

[TechTrend]

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