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!

 

[Jon von der Heyden]

Hi

Try;
=--TEXT(A1,"00-00-00")

And format the cell as a date.

 

[Rick Rothstein]

Hi

Try;
=--TEXT(A1,"00-00-00")

And format the cell as a date.

That won't work because in order to use the dashes, the numbers must be in YYYYMMDD order (which they aren't)(note the 4-digit year requirement also). This should work if the OP's date order is d/mm/yy...

=--TEXT(A2,"0\/00\/00")

 

[Weazel]

Just an observation but I believe the format should be MM/DD/YYYY.

I came up with this but I'm sure there's a better way

=DATEVALUE(MID(A1,FIND(0,A1),2)&"/"&LEFT(A1,FIND(0,A1)-1)&"/"&SUBSTITUTE(RIGHT(A1,2),12,2012))

formatted as mmmm dd, yyyy

 

[Jonmo1]

There seems to be some issues with regional settings on these formulas.
Jon's works on UK settings
Neither works on US settings for the 3rd date 250812

This seems to work regardless of regional settings

=DATE(19+(RIGHT(A1,2)+0<35)&RIGHT(A1,2)+0,MID(A1,LEN(A1)-3,2),LEFT(A1,LEN(A1)-4))

With 35 being the cutoff year between a 1900 and 2000 dates.
Less than 35 = 2000
35+ = 1900

 

[Rick Rothstein]

This seems to work regardless of regional settings

=DATE(19+(RIGHT(A1,2)+0<35)&RIGHT(A1,2)+0,MID(A1,LEN(A1)-3,2),LEFT(A1,LEN(A1)-4))

I think this shorter formula (two less function calls also) might work regardless of the regional settings too...

=1*REPLACE(A1,LEN(A1)-3,2,TEXT(28*MID(A1,LEN(A1)-3,2),"mmm"))

 

[catchg]

Thanks to all who responded to my thread. It's amazing how one can spend days struggling to figure something out and within minutes of asking for help it's now all over.

Although I tried all of your suggestions, jonmo1's solution works perfectly for all of the possible date ranges in my data set. Thanks!

 

[Jonmo1]

Glad to help, thanks for the feedback.

 

[catchg]

Thank you Rick. Your solution works as well in addition to jonmo1. Regardless of regional settings.... Thank You!

 

[Jon von der Heyden]

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")