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 Data>Text to Columns>Next>Next>Column Data Format DMY


Gord Dibben MS Excel MVP

On Thu, 4 Mar 2010 08:43:08 -0800 (PST), rjagathe <(E-Mail Removed)>
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.
> .
>

On Mar 4, 11:08*pm, FSt1 <F...@discussions.microsoft.com> 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 Data>Text to Columns>Next>Next>Column Data Format DMY
>
> Gord Dibben *MS Excel MVP
>
> On Thu, 4 Mar 2010 08:43:08 -0800 (PST), rjagathe <rjaga...@gmail.com>
> 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 <(E-Mail Removed)>
wrote:

>On Mar 4, 10:50*pm, Gord Dibben <gorddibbATshawDOTca> wrote:
>> Try Data>Text to Columns>Next>Next>Column Data Format DMY
>>
>> Gord Dibben *MS Excel MVP
>>
>> On Thu, 4 Mar 2010 08:43:08 -0800 (PST), rjagathe <rjaga...@gmail.com>
>> 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