earliest date having certain value

[dmarkell]

i have a list of dates in column A:

7/1/07
8/1/07
9/1/07
10/1/07
....
8/1/37

in a different cell i want to return the earliest (smallest) date from column A that is either an 8/1 or a 2/1 (from which i will used edate to produce the full list of semi-annual dates between 7/1/07 and 8/1/37 keyed off of final cell in column A). any ideas? thanks.

 

[barry houdini]

Try

=MIN(IF(ISNUMBER(MATCH(TEXT(A1:A100,"d-mmm"),{"1-feb","1-aug"},0)),A1:A100))

confirmed with CTRL+SHIFT+ENTER

format as date

 

[dave3009]

Could MIN and MAX help you?

 

[Perrister]

Using this formula, how do I get all dates to work. The only ones that work now have to be in the {"1-feb","1-aug"} or does not return the value.

I added a calendar looking forward 8 years, as my MIN or MAX date could be any day and named the range "Dates" and tried to replace {"1-feb","1-aug"} with {Dates} and {"Dates"} but it will not work.

Any ideas?


=MIN(IF(ISNUMBER(MATCH(TEXT(A1:A100,"d-mmm"),{"1-feb","1-aug"},0)),A1:A100))

 

[Aladin Akyurek]

Using this formula, how do I get all dates to work. The only ones that work now have to be in the {"1-feb","1-aug"} or does not return the value.

I added a calendar looking forward 8 years, as my MIN or MAX date could be any day and named the range "Dates" and tried to replace {"1-feb","1-aug"} with {Dates} and {"Dates"} but it will not work.

Any ideas?


=MIN(IF(ISNUMBER(MATCH(TEXT(A1:A100,"d-mmm"),{"1-feb","1-aug"},0)),A1:A100))

If a year test is not important or irrelevant...

Control+shift+enter, not just enter:

=MIN(IF(ISNUMBER(MATCH(TEXT(A1:A100,"d-mmm"),List,0)),A1:A100)

where List stands for a separate range which must house relevant items like 1-feb, 1-aug, etc. as text values.

 

[Perrister]

Sorry does not work, returns 0.

I have formatted all date fields to d-mmm, still nothing.

Thanks,
Perry

 

[Aladin Akyurek]

Sorry does not work, returns 0.

I have formatted all date fields to d-mmm, still nothing.

Thanks,
Perry

What does not work?

 

[Perrister]

=MIN(IF(ISNUMBER(MATCH(TEXT(E18:E88,"d-mmm"),Dates,0)),E18:E88))

cell shows 0-Jan-00

Dates refers to 8 year Calendar off of this sheet.

Thanks,
Perry