New kind of date format problem

Posted by Jeff on September 20, 2001 3:10 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 Richard S on September 20, 2001 5:37 PM

I assume you are in a time zone that uses that format. I tried this in Australia and it didn't work, but when I changed the order to the same as our format, it did, so I assume in this order it will work there. Let me know.

=CONCATENATE((MID(G23,3,2)*1),"/",(RIGHT(G23,2)*1),"/",(LEFT(G23,2)*1))

HTH
Richard

Posted by Richard S on September 20, 2001 5:41 PM

Sorry, you need to multiply the whole thing by 1. COuld also use
=(MID(G23,3,2)&"/"&RIGHT(G23,2)&"/"&LEFT(G23,2))*1
Both assume your date 990228 are in cell G23
Richard

Posted by Jeff on September 21, 2001 5:02 AM

Thanks for your help, unfortunately it did not work because when I have

000123 that should display Jan 23, 2000 instead I get "Jan 12, 2012" you will provably get the same result.

I have try many different ways. For some reason it is not recognizing the Zeros to the left of the number.

Posted by Jeff on September 21, 2001 5:35 AM

Finally got it. It's a long formula but it works.

=DATE(LEFT(RIGHT(CONCATENATE("'0000",D78),6),2), MID(RIGHT(CONCATENATE("'0000",D78),6),3,2),RIGHT(RIGHT(CONCATENATE("'0000",D75),6),2))

Thanks. Thanks for your help, unfortunately it did not work because when I have 000123 that should display Jan 23, 2000 instead I get "Jan 12, 2012" you will provably get the same result. I have try many different ways. For some reason it is not recognizing the Zeros to the left of the number.

: Sorry, you need to multiply the whole thing by 1. COuld also use : =(MID(G23,3,2)&"/"&RIGHT(G23,2)&"/"&LEFT(G23,2))*1 : Both assume your date 990228 are in cell G23 : Richard