I have a column of dates (mm/dd/yyyy) in which I want to find the first
occurrence of any date in 2005. The following finds the last occurrence in
the column: LOOKUP(2005,YEAR($A$2:$A$114),$A$2:$A$114). Anyone have any
ideas how I might get the first occurrence? Thanks.

On Sun, 10 Dec 2006 04:56:00 -0800, mpierre
wrote:

I have a column of dates (mm/dd/yyyy) in which I want to find the first
occurrence of any date in 2005. The following finds the last occurrence in
the column: LOOKUP(2005,YEAR($A$2:$A$114),$A$2:$A$114). Anyone have any
ideas how I might get the first occurrence? Thanks.


The **array** formula (entered with <ctrl<shift<enter) will give the first
occurrence.

In this case, "first" means the date on the lowest numbered row in which a date
from 2005 appears. RNG cannot be a full column.

=INDEX(RNG,MATCH(TRUE,(YEAR(RNG)=2005),0))

If you want, instead, the "earliest" date in 2005 in that column, then use this
**array** formula:

=MIN(IF(YEAR(RNG)=2005,RNG))

If the dates are arranged in sorted order, ascending, the two formulas will
give the same results.


--ron

or this array
=INDEX(rng,MATCH(2005,YEAR(rng),0))

--
Don Guillett
SalesAid Software

"Ron Rosenfeld" wrote in message
...
On Sun, 10 Dec 2006 04:56:00 -0800, mpierre

wrote:

I have a column of dates (mm/dd/yyyy) in which I want to find the first
occurrence of any date in 2005. The following finds the last occurrence
in
the column: LOOKUP(2005,YEAR($A$2:$A$114),$A$2:$A$114). Anyone have any
ideas how I might get the first occurrence? Thanks.


The **array** formula (entered with <ctrl<shift<enter) will give the
first
occurrence.

In this case, "first" means the date on the lowest numbered row in which a
date
from 2005 appears. RNG cannot be a full column.

=INDEX(RNG,MATCH(TRUE,(YEAR(RNG)=2005),0))

If you want, instead, the "earliest" date in 2005 in that column, then use
this
**array** formula:

=MIN(IF(YEAR(RNG)=2005,RNG))

If the dates are arranged in sorted order, ascending, the two formulas
will
give the same results.


--ron