Find Earliest Date based on conditions

I'm trying to create a formula to return the earliest date from a column of dates when a date in the same row of an adjacent column is not present.

For example:

Col A Col B
1/1/07 1/2/07
1/15/07
1/31/07 1/28/07
2/15/07
9/15/07

In this case I would want to return 1/15/07. The earliest date in Col A Where Col B hasn't been filled in yet.

I've tried If statments with MIN functions in there. Tried several variations as Array formulas, but have only been succesful so far in returning the minimum date in col A (1/1/07).

Any help much appreciated.

Thanks,

Shred

Thank you for your reply.

I pasted that into my workbook and adjusted the ranges accordingly. It only works for me though if the range containing the dates is sorted in Ascending order. Really I'm looking for a way to accomplish this in one cell without having to have the array sorted in any particular manner.

I've accomplished what I'm hoping to achieve by calculating a thrid column that fills in the dates dependent on the test and then doing a MIN on that range. That works, but I'm trying to do it all in one cell on a Summary Sheet that is a different sheet from where the data resides (same workbook).

In essence, I've been trying to construct an array formula that will dynamically generate an array of dates that correspond to the test criteria, and then throw that array to the MIN function to pull out the earliest date, But I havne't been successful with that yet.

I can't assume that the dates will always be sorted.

Thanks again for your reply.

Shred

I had tried that and variations to it, but I hadn't been successful in accounting for the circumstance of the first column containing a blank. In that case, the formula you suggested returns 1/0/1900.

So I tried things like:

=MIN(IF(AND(D16:D35="",C16:C35<>""),C16:C35),"")) (as an array formula)

to no avail.

to make it easier to discuss, column C is Due Date, Column D is Completed Date.

Im trying to pull the "Next Due Date", which I'd define as the earliest Due Date in the list that doesn't have a Completion Date filled in next to it yet.

Thanks again for any insight.

Shred

That last one got it. Thanks.

I'm curious now though why the AND function didn't create the same result as the two nested if queries in your formula. Does the AND not work inside the Array Formula?

Thank you for your time and help. I've now expanded this formula's concept throughout several positions in a workbook I'm creating.

Shred