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.

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


"k3639" wrote in message
...
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.

Select the range, do datatext 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



"k3639" wrote in message
...
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.

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


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=565805

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.

"Trevor Shuttleworth" wrote:

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


"k3639" wrote in message
...
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.

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.

"Peo Sjoblom" wrote:

Select the range, do datatext 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



"k3639" wrote in message
...
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.

DataText to ColumnsNextNextColumn Data FormatDateYMD

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


Gord Dibben MS Excel MVP

On Thu, 27 Jul 2006 14:23:02 -0700, k3639
wrote:

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.

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

"VBA Noob" wrote:


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


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=565805