find first occurrence of data in a column
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. |
find first occurrence of data in a column
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 |
find first occurrence of data in a column
|
All times are GMT +1. The time now is 12:45 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com