Earliest date in a row that may contain 1/0/1900 (zero)

[KeithM]

I've read the other posts about finding the earliest date in a row.
However, in my case, I have 20 columns of dates in the row.
The first column does not necessarily contain the earliest date.
Once there is no legitimate date for a column, the remaining columns
are filled with 1/0/1900 (zero).
Using =MIN(range) always produces 0.

Example of data:
[TABLE="width: 430"]
<tbody>[TR]
[TD="class: xl65, width: 86, align: right"]9/9/2004[/TD]
[TD="class: xl65, width: 86, align: right"]9/14/2004[/TD]
[TD="class: xl65, width: 86, align: right"]9/8/2004[/TD]
[TD="class: xl65, width: 86, align: right"]1/0/1900[/TD]
[TD="class: xl65, width: 86, align: right"]1/0/1900[/TD]
[/TR]
</tbody>[/TABLE]

Your assistance is greatly appreciated.

 

[shawnhet]

"=MIN(range) always produces 0."

That will only be true when there is a date of 0 - Are you looking for minimum nonzero date?

If so you can try:

=SMALL(A1:D100,COUNTIF($A$1:$D$100,0)+1)

This expression counts all the 0s in a range and returns the smallest number that is not a zero.

Cheers,

 

[JLGWhiz]

Or, usin the actual cell entries.
You will need to fit this to your worksheet range.

=SMALL(A2:A6,COUNTIF(A2:A6, "1/1/1900")+1)

 

[KeithM]

Dear Shawnhet,
OUTSTANDING!
I need to look at this formula closer to figure out what it's doing - first time I've ever seen the "SMALL" command.
Worked perfectly.
Thank you again.
You made my day!

 

[shawnhet]

No worries. Glad it worked out for you and thanks for the feedback.

Just FYI, the SMALL command searches through a range and finds a number that is some number away from the smallest

The countif portion I inserted then counted all the zeros and added 1, ie so if there are 10 zeros in your range the small command will return the eleventh smallest number in your range.

Cheers,

 

[KeithM]

Brilliant!