I download a query that has a column of dates displayed without the
seperators (20040830). When I try to convert these cells to reflect a date,
nothing changes. No matter what I tell the format to llok like, it remains as
20040830. I can only change this to 2004/08/30 or 30/08/2004 by physically
going in and changing it in each cell.

suppose the date_text (0040830 is in A1

enter in B1
=LEFT(A1,4)
enter in C1
=MID(A1,5,2)
enter in D1
=RIGHT(A1,2)
enter in E1
=DATE(B1,C1,D1)

you get in E1 mm/dd/yy
if other date_texts are A2 down
copy B1, C1,D1 and E1 down

there may be more elegant solutions




"GRABrendan" wrote in message
...
I download a query that has a column of dates displayed without the
seperators (20040830). When I try to convert these cells to reflect a
date,
nothing changes. No matter what I tell the format to llok like, it remains
as
20040830. I can only change this to 2004/08/30 or 30/08/2004 by physically
going in and changing it in each cell.

"R.VENKATARAMAN" wrote:

suppose the date_text (0040830 is in A1

enter in B1
=LEFT(A1,4)
enter in C1
=MID(A1,5,2)
enter in D1
=RIGHT(A1,2)
enter in E1
=DATE(B1,C1,D1)

you get in E1 mm/dd/yy
if other date_texts are A2 down
copy B1, C1,D1 and E1 down

there may be more elegant solutions




"GRABrendan" wrote in message
...
I download a query that has a column of dates displayed without the
seperators (20040830). When I try to convert these cells to reflect a
date,
nothing changes. No matter what I tell the format to llok like, it remains
as
20040830. I can only change this to 2004/08/30 or 30/08/2004 by physically
going in and changing it in each cell.


Thank you very much, that helped me get just what I wanted.

Select the column of dates. Then use Data/Text to Columns. On the first 2
dialogs, click Next. On the 3rd, select Date and the format YMD. Then click
Finish.

On Mon, 26 Sep 2005 03:02:02 -0700, "GRABrendan"
wrote:



"R.VENKATARAMAN" wrote:

suppose the date_text (0040830 is in A1

enter in B1
=LEFT(A1,4)
enter in C1
=MID(A1,5,2)
enter in D1
=RIGHT(A1,2)
enter in E1
=DATE(B1,C1,D1)

you get in E1 mm/dd/yy
if other date_texts are A2 down
copy B1, C1,D1 and E1 down

there may be more elegant solutions




"GRABrendan" wrote in message
...
I download a query that has a column of dates displayed without the
seperators (20040830). When I try to convert these cells to reflect a
date,
nothing changes. No matter what I tell the format to llok like, it
remains
as
20040830. I can only change this to 2004/08/30 or 30/08/2004 by
physically
going in and changing it in each cell.


Thank you very much, that helped me get just what I wanted.

Mr. Brendan-Ms. Mryna Larson's solution is much more elegant than mine


==============================
"Myrna Larson" wrote in message
...
Select the column of dates. Then use Data/Text to Columns. On the first 2
dialogs, click Next. On the 3rd, select Date and the format YMD. Then
click
Finish.

On Mon, 26 Sep 2005 03:02:02 -0700, "GRABrendan"
wrote:



"R.VENKATARAMAN" wrote:

suppose the date_text (0040830 is in A1

enter in B1
=LEFT(A1,4)
enter in C1
=MID(A1,5,2)
enter in D1
=RIGHT(A1,2)
enter in E1
=DATE(B1,C1,D1)

you get in E1 mm/dd/yy
if other date_texts are A2 down
copy B1, C1,D1 and E1 down

there may be more elegant solutions




"GRABrendan" wrote in message
...
I download a query that has a column of dates displayed without the
seperators (20040830). When I try to convert these cells to reflect a
date,
nothing changes. No matter what I tell the format to llok like, it
remains
as
20040830. I can only change this to 2004/08/30 or 30/08/2004 by
physically
going in and changing it in each cell.


Thank you very much, that helped me get just what I wanted.