Date from text

[joeb]

I need to return the oldest of three dates that are presented as text
"mmddyy" i.e."030107" "061493" "110295". If I use MIN, "030107" will return,
if I use MAX obviously "110295" will return. Is there a way I can turn this
text into the proper date so I can use MIN, then use =TEXT (A1,"MMDDYY") to
get it back to text?
Thanks.

 

[Gary''s Student]

The equation depends upon whether 030107 is
1 March 1907
or
1 March 2007

 

[joeb]

Right - 2007 - but I also have 1995 for example.
Thanks again.

 

[Gary''s Student]

Put your text values in B1 thru B3. In A1 thru A3 enter:

=DATE(2000+RIGHT(B1,2),LEFT(B1,2),MID(B1,3,2))
=DATE(1900+RIGHT(B2,2),LEFT(B2,2),MID(B2,3,2))
=DATE(1900+RIGHT(B3,2),LEFT(B3,2),MID(B3,3,2))

This is what we see:

3/1/2007 030107
6/14/1993 061493
11/2/1995 110295

Finally, elsewhere in the worksheet we enter:

=DATE(2000+RIGHT(B1,2),LEFT(B1,2),MID(B1,3,2))

and see: 061493 displayed

NOTE that the formula in A1 has a different year than the formulas in A2 and
A3. This is because we still need some kind of rule to give Excel so it can
know if the 2 digit year refers to 1900 or 2000.

 

[joeb]

Thank you for your help. I should have been more clear. Dates are in a row
and I need the oldest. Part numbers (about 3,000 of them) are in a column.
Column headers are:

PART## DATELOC1 DATELOC2 DATELOC3 OLDESTDATE

 

[T. Valko]

I need to return the oldest of three dates that are presented as text

"mmddyy" i.e."030107" "061493" "110295". If I use MIN, "030107" will
return,
if I use MAX obviously "110295" will return.

The oldest date is "061493" so if you could use MIN it would return
"061493".
"030107" is the most recent date so if you could use MAX it would return
"030107".

If you want the oldest date then you want the MIN date, right?

We have to figure out a means of identifying the century. What is the
*oldest* year in use? Are any dates older than 1970? 1980? 1985?

 

[JMJ]

Is there a reason why you cannot use the MMDDYYYY?
You can always copy the columns to another part of your worksheet, format
them as date and then use the MIN function. ....