New kind of date format problem

Posted by Jeff on September 20, 2001 3:09 PM

Im Going nuts, please help.

I have a column with dates in the following format YYMMDD:
Using =DATE(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2))
I GET:

When: I Get: I need
990228 02/28/99 02/28/99
000121 01/21/12 01/21/00
001220 08/20/13 12/20/01
010212 09/12/11 02/12/01

I like to convert these date to: MM/DD/YY

Please help!

Thanks,

Miami

Posted by Jay on September 20, 2001 5:50 PM

Posted by Jay on September 20, 2001 6:11 PM

This is sort of a round about way of doing it, but it appears to work. Assuming YYMMDD is in cell A1, try this;

=DATEVALUE(MID(A1,3,2)&"-"&RIGHT(A1,2)&"-"&LEFT(A1,2))

Jay

Posted by Jaime on September 20, 2001 6:14 PM

=DATE(LEFT(A1+19000000+1000000*(LEN(A1)&LT;&GT;6),4),MID(A1+19000000,5,2),RIGHT(A1+19000000,2))

Hey Jeff,

I know it's long but it works. Just copy paste this in excel. It assumes the date ur decoding is in cell A1