Pick the earliest date from a selection of dates...

Hi all,

There is a speadsheet at work with a column which has a range of dates (from 18/05/2003 - date) which information gets drawn from every Friday. The information needed from the column is pretty simple but lengthy to expalin. For example...

http://xs511.xs.to/xs511/07043/shot1.png

As you can the dates are in no order...and need to stay that way. The information needed every week is shown here...

http://xs311.xs.to/xs311/07043/shot2.png

I have got the formula for the number of enteries between each date but strugling with the earliest date within the dates. I can do it manually by auto filtering the column and looking for the date closest to the earliest date but this is long.

The dates change on a weekly basis...just to throw a spanner in the works.

Any help offered is appreciated.

Thank You.

That would work for the whole of the dates but when I want the earliest date between 20/10/2005 - 29/11/205 it wouldnt help.

Thanks for the thought though. I also suspect that the MIN function will be used somewhere in the answer...if I ever get one!

Emma x

Something like :
=MIN(IF((List>=LLim)*(List<=ULim),List,FALSE))

I tried this on a dummy i made:

=MIN(IF((D1:D4>=A1)*(D1:D4<=B1),D1:D4,FALSE))

and got the good old "#VALUE" error result...

Forgot to say, it's an array formula Enter it with Ctrl+Shift+Enter in the formula bar

Thank you...Your a life saver

Just a little problem...

If the result is 0...as the cell is formatted into dd/mm/yy...i get "00/01/1900"...

Maybe an if statement needed?

Are there 0's in your list ? Otherwise I suppose there would always be a minimum ( except if you exclude the limits.
Then the IF function will help

There isnt "0"'s in my list...
But some of the dates we have to report on will not apear on the list...so a nil return needs to show. eg. 06/04/2003 - 10/04/2003 may not have any entries on the list...so a "0" needs to be displayed...but as the cell is formatted as dd/mm/yy...is a "0" is impossible?

Emma x

Hi,

Try your formula without the false argument:

=MIN(IF((D1:D4>=A1)*(D1:D4<=B1),D1:D4))

enter using Ctrl+Shift+Enter


HTH
Jean-Guy