I receive Excel data from my branch offices.the data should contain
date coloumns.But some cells contain dates in "<year<month.<date"
format i.e.,21/02/2010 in dd/mm/yyyy format is displayed as
20100221.Why it is happening?How to convert them into dd/mm/yyyy
format?
I tried to record and run a macro to insert "/" between year and month
and between month and date,then clicking "Enter" button...But,it
displays same date in all the cells in which I run the macro.

Try DataText to ColumnsNextNextColumn Data Format DMY


Gord Dibben MS Excel MVP

On Thu, 4 Mar 2010 08:43:08 -0800 (PST), rjagathe
wrote:

I receive Excel data from my branch offices.the data should contain
date coloumns.But some cells contain dates in "<year<month.<date"
format i.e.,21/02/2010 in dd/mm/yyyy format is displayed as
20100221.Why it is happening?How to convert them into dd/mm/yyyy
format?
I tried to record and run a macro to insert "/" between year and month
and between month and date,then clicking "Enter" button...But,it
displays same date in all the cells in which I run the macro.

hi
try a formula.
=MID(B6,5,2)&"/"&RIGHT(B6,2)&"/"&LEFT(B6,4)
worked for me.
put the formula in a blank formula and copy down.
then copy the helper column and paste special values.

regards
FSt1

"rjagathe" wrote:

I receive Excel data from my branch offices.the data should contain
date coloumns.But some cells contain dates in "<year<month.<date"
format i.e.,21/02/2010 in dd/mm/yyyy format is displayed as
20100221.Why it is happening?How to convert them into dd/mm/yyyy
format?
I tried to record and run a macro to insert "/" between year and month
and between month and date,then clicking "Enter" button...But,it
displays same date in all the cells in which I run the macro.
.

Posting the same Q 3 times ..?

1) Depends if the data is a String or Number Value ..?

Copy Data to All 3 Columns (Day/Month/Year) then format each
accordingly.

or it may be as easy as re-formatting the column..!
Select Column.. Right Click.. Format Cells..
Date.. then select the format you want
or
Custom Format.. and make your own preferred format up ..!

HTH
Andrew ;-)

"rjagathe" wrote in message
...
|I receive Excel data from my branch offices.the data should contain
| date coloumns.But some cells contain dates in "<year<month.<date"
| format i.e.,21/02/2010 in dd/mm/yyyy format is displayed as
| 20100221.Why it is happening?How to convert them into dd/mm/yyyy
| format?
| I tried to record and run a macro to insert "/" between year and
month
| and between month and date,then clicking "Enter" button...But,it
| displays same date in all the cells in which I run the macro.

On Mar 4, 11:08*pm, FSt1 wrote:
hi
try a formula.
=MID(B6,5,2)&"/"&RIGHT(B6,2)&"/"&LEFT(B6,4)
worked for me.
put the formula in a blank formula and copy down.
then copy the helper column and paste special values.

regards
FSt1

hi
I put 19980427 in cell B1 and put your formula in A1.But A1 becomes
19980427 only.It does not return 27/04/1998.

regards
rjagathe

"rjagathe" wrote:
I receive Excel data from my branch offices.the data should contain
date coloumns.But some cells contain dates in "<year<month.<date"
format i.e.,21/02/2010 in dd/mm/yyyy format is displayed as
20100221.Why it is happening?How to convert them into dd/mm/yyyy
format?
I tried to record and run a macro to insert "/" between year and month
and between month and date,then clicking "Enter" button...But,it
displays same date in all the cells in which I run the macro.
.

On Mar 4, 10:50*pm, Gord Dibben <gorddibbATshawDOTca wrote:
Try DataText to ColumnsNextNextColumn Data Format DMY

Gord Dibben *MS Excel MVP

On Thu, 4 Mar 2010 08:43:08 -0800 (PST), rjagathe
wrote:



I receive Excel data from my branch offices.the data should contain
date coloumns.But some cells contain dates in "<year<month.<date"
format i.e.,21/02/2010 in dd/mm/yyyy format is displayed as
20100221.Why it is happening?How to convert them into dd/mm/yyyy
format?
I tried to record and run a macro to insert "/" between year and month
and between month and date,then clicking "Enter" button...But,it
displays same date in all the cells in which I run the macro.

Hi
when I tried this, for whatever year I gave, it returns year as 1905
only.

with regards
rjagathe

Typo...............YMD should have been the format.


Gord

On Fri, 5 Mar 2010 23:35:33 -0800 (PST), rjagathe
wrote:

On Mar 4, 10:50*pm, Gord Dibben <gorddibbATshawDOTca wrote:
Try DataText to ColumnsNextNextColumn Data Format DMY

Gord Dibben *MS Excel MVP

On Thu, 4 Mar 2010 08:43:08 -0800 (PST), rjagathe
wrote:



I receive Excel data from my branch offices.the data should contain
date coloumns.But some cells contain dates in "<year<month.<date"
format i.e.,21/02/2010 in dd/mm/yyyy format is displayed as
20100221.Why it is happening?How to convert them into dd/mm/yyyy
format?
I tried to record and run a macro to insert "/" between year and month
and between month and date,then clicking "Enter" button...But,it
displays same date in all the cells in which I run the macro.

Hi
when I tried this, for whatever year I gave, it returns year as 1905
only.

with regards
rjagathe