# earliest date having certain value

#### dmarkell

##### New Member
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.

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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

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

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.

Sorry does not work, returns 0.

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

Thanks,
Perry

Sorry does not work, returns 0.

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

Thanks,
Perry

What does not work?

=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

Replies
13
Views
295
Replies
1
Views
330
Replies
3
Views
433
Replies
32
Views
865
Replies
4
Views
241

### Forum statistics

1,221,052
Messages
6,157,628
Members
451,426
Latest member
VinnyDoesntKnowExcelCode

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back