Dates converted into text
in [Programming]
|
From: rjagathe on 4 Mar 2010 11:43 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.
From: Gord Dibben on 4 Mar 2010 12:50 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 <rjagathe(a)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.
From: FSt1 on 4 Mar 2010 13:08 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. > . >
From: Andrew on 4 Mar 2010 13:32 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" <rjagathe(a)gmail.com> wrote in message news:42c83be8-6462-4020-ae9e-f82722869ab0(a)f17g2000prh.googlegroups.com... |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.
From: rjagathe on 6 Mar 2010 02:28
On Mar 4, 11:08 pm, FSt1 <F...(a)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. > > . |