Help with converting date formats
 

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.

Help with converting date formats
 

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.

Help with converting date formats
 

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.

Help with converting date formats
 

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

Help with converting date formats
 

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.

Help with converting date formats
 

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.

Help with converting date formats
 

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.

Help with converting date formats
 

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