Using this formula for all dates.....

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

Thanks,
Perry

 

[DonkeyOte]

If you're using a precedent range this would take the place of the in line array, ie:

=MIN(IF(ISNUMBER(MATCH(TEXT(A1:A100,"d-mmm"),Dates,0)),A1:A100)) 
confirmed with CTRL + SHIFT + ENTER

the above will return the earliest of the dates listed in A1:A100 which can be found in Dates named range (d-mmm basis - year irrelevant in terms of qualification)