Find the position of a date
 

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

Find the position of a date
 

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

On Apr 3, 6:57*pm, TechTrend wrote:
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

Find the position of a date
 

On Apr 3, 6:57 pm, TechTrend wrote:
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).

On Apr 3, 1:02*pm, Pete_UK wrote:
=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.

Find the position of a date
 

On Apr 3, 10:22*pm, joeu2004 wrote:
On Apr 3, 6:57 pm, TechTrend wrote:

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

On Apr 3, 1:02*pm, Pete_UK wrote:

=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

Find the position of a date
 

On Apr 3, 10:39*pm, TechTrend wrote:
On Apr 3, 10:22*pm, joeu2004 wrote:



On Apr 3, 6:57 pm, TechTrend wrote:

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

On Apr 3, 1:02*pm, Pete_UK wrote:

=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

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

Find the position of a date
 

On Apr 8, 5:42*am, JIL wrote:
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.

Mika wrote:
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")

Find the position of a date
 

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