Date Conversion

[catchg]

I have a column of imported data that represents dates. An example of the data follows:

10812 which is supposed to be the date August 1, 2012
50812 which is supposed to be the date August 5, 2012
250812 which is supposed to be the date August 25, 2012

Please note that there are not any leading zeros in the raw, imported data.

I would like to have in a second column a formula that will convert the data to mm/dd/yy.

So, how can I convert the 5 digit value 10812 in cell A2 to display the date 08/01/12 in cell B2 while also keeping in mind that 6 digit data like 250812 needs to be converted to 08/25/12 as well.

Any assistance would be greatly appreciated. Thanks in Advance!

 

[Jonmo1]

Not wanting to be left out of all this good praise ; does this work on US settings?

=--TEXT(REPLACE(RIGHT(A1,4),3,0,LEFT(0&A1,2)),"00\/00\/00")

Not quite, 250812 comes out as Aug 2nd 2012

 

[Jon von der Heyden]

Not quite, 250812 comes out as Aug 2nd 2012

Doh!

=--TEXT(REPLACE(RIGHT(A3,4),3,0,LEFT(TEXT(A3,"000000"),2)),"00\/00\/00")

 

[Jonmo1]

Sorry Jon, still not working.
Works in US settings
In UK:
10812 = January 8 2012
50812 = May 8 2012
250812 = #VALUE!

 

[Jon von der Heyden]

Sorry Jon, still not working.
Works in US settings
In UK:
10812 = January 8 2012
50812 = May 8 2012
250812 = #VALUE!

Yes I was after a US solution. I believe my 1st suggestion works for UK (my machine is on UK settings).

 

[Rick Rothstein]

Doh!

=--TEXT(REPLACE(RIGHT(A3,4),3,0,LEFT(TEXT(A3,"000000"),2)),"00\/00\/00")

Yes, that works here in the US, but does it work when the regional setting is for d/m/y (I would think not as it is my understanding that the slash format gets interpretted according to the computers regiona/locale setting)?

 

[Jonmo1]

This is why it's best to use Unambiguous solutions, like using The Date function.
Or some method that actually spells out the name of the month, like Rick did in post #6

 

[Jon von der Heyden]

This is why it's best to use Unambiguous solutions, like using The Date function.
Or some method that actually spells out the name of the month, like Rick did in post #6

Yup - the formula that works on both settings is a clear winner I just didn't want to be the guy that only offered a formula that doesn't work; albeit it works on UK setting.

 

[catchg]

Jon, using your solution works for lets say 010113 (yielding 01/01/13) but does not work on say 250113 (it yields 01/02/13 instead of 01/25/13). Or lets say 121213 yields 12/01/13 instead of 12/12/13. Sorry but no praise for you ...But works in UK