Thread: 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.

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

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" <k3639@discussions.microsoft.com> wrote in message
news:609D23A8-3060-4815-86FC-7DAE2939A8A5@microsoft.com...
>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 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



"k3639" <k3639@discussions.microsoft.com> wrote in message
news:609D23A8-3060-4815-86FC-7DAE2939A8A5@microsoft.com...
>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, 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" <k3639@discussions.microsoft.com> wrote in message
> news:609D23A8-3060-4815-86FC-7DAE2939A8A5@microsoft.com...
> >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 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
>
>
>
> "k3639" <k3639@discussions.microsoft.com> wrote in message
> news:609D23A8-3060-4815-86FC-7DAE2939A8A5@microsoft.com...
> >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.
> >
> >
>
>
>

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

On Thu, 27 Jul 2006 14:23:02 -0700, k3639 <k3639@discussions.microsoft.com>
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
>
>