Help with converting date formats

[k3639]

I have a column of data that is formatted with general text in yymmdd format,
and for the purposes of a formula, I need it to represent in date format of
mm/dd/yy. I have tried formatting the column, using datevalue, edate,
selecting the column and converting the text to column, and no matter what I
do it seems to skew the end resulting dates way off from what they should be,
for example, the first row is 680126, and I need it to appear as 01/26/1968,
but when I attempt to reformat, or perform any modification on the cell, it
throws the value to 02/12/62?? And in the formula line it shows 2/12/3762.
Can anyone help with this please? The column in question has over 1300 lines
in it, and I don't want to have to rekey the dates.

 

[Trevor Shuttleworth]

If the date is in cell A1, use the formula:

=DATE(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2))

680126 becomes 26/01/1968; format as "mm/dd/yyy" to get 01/26/1968.
You might have that format as the default where you live ...

Drag the formula down the column to repeat.

Regards

Trevor

 

[Peo Sjoblom]

Select the range, do data>text to columns, click next twice and under column
data format select dates and YMD and click finish

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com

 

[VBA Noob]

One way

In B1
=RIGHT(A1,2)&"/"&MID(A1,3,2)&"/"&LEFT(A1,2)

In C1

=TEXT(B1,"mm/dd/yy")


or just this in B1

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

VBA Noob

 

[k3639]

Thanks for the response, actually, the data is in column AE beginning with
row 2, and when I tried this formula I kept getting 0 values on the left and
right, and an invalid for the mid. Not quite sure what the problem is.

 

[k3639]

Thanks for the response, I have tried that already, and that is what skews my
values way off, either that, or makes no change whatsoever.

 

[Gord Dibben]

Data>Text to Columns>Next>Next>Column Data Format>Date>YMD

680126 returns 01/26/1968 formatted as mm/dd/yyyy


Gord Dibben MS Excel MVP

 

[k3639]

Thanks to all, this worked for me, and with slight modification I was able to
calculate the entire spreadsheet. Thanks again!