Date from text - clarified

[joeb]

Dates are formated as text (i.e. "031597" for March 15, 1997 or "040806" for
January 8, 2006) in a row - let's say in columns B and C. I need the oldest
date from the row.

 

[Gary''s Student]

I am sorry I did not make myself clear. To find the oldest date, we need to
know how to interpret the year.

If the formula sees:

111140

It can either interpret this as November 11 1940 or November 11 2040.


If the formula sees:

122550

It can either interpret this as December 25 1950 or December 25 2050.


If you (the human) look at a piece of data, you determine the correct
century by applying some "rule".

The formula must be designed with the rule in mind!

 

[JMJ]

Dates are formated as text (i.e. "031597" for March 15, 1997 or "040806" for
January 8, 2006) in a row - let's say in columns B and C. I need the oldest
date from the row.

Why don't you format the column as date and then get the MIN of the column?

 

[T. Valko]

Try this:

=MIN(INDEX(--TEXT(B1:C1,"00\/00\/00"),,1))

Format as DATE

Years 00 to 29 will be evaluated as century 20.

Years 30 to 99 will be evaluated as century 19.