find first occurrence of data in a column

[Guest]

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.

 

[Ron Rosenfeld]

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

 

[Don Guillett]

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