Excel. Convert date column from 19980114 to 14/01/1998

The quickest way that i can tell you to do the same, is go to data-->text to columns-->Delimeted-->Hit Next-->Dont do anything in this just HIT next again-->In this select date-->YMD and hit finish.
There you go your data changed.
Saurabh...

you can also do it by a formula like so:

=RIGHT(A1,2)&"/"&MID(A1,5,2)&"/"&LEFT(A1,4)

saurabh726,

I selected the column and did what you said. the task complete ok but did not change the data.

broomee9,

How exactly do I use this formula? I selected the column and then entered =RIGHT(A1,2)&"/"&MID(A1,5,2)&"/"&LEFT(A1,4) in the fx box and Entered, nothing happened.

Did you selected the whole column data before going to text to columns as that will change your data in 1 go...
Saurabh...

Yes, I clicked the letter A and now the whole column is highlighted blue.  I run through the suggested procedure with all the defaults except selecting DMY at the end. changed nothing.

Don't select DMY it should be YMD and select the date option in the last step.
Saurabh..

Saurabh's suggestion works for me....
Alternative with formula....
If you have data starting at C2 then put this formula in D2
=TEXT(C2,"0000-00-00")+0
Use Format > Cells > Number and set format of cell to required date format, e.g. dd/mm/yyyy then copy the formula down the column. You can do that quickly by putting the cursor on the bottom right of the cell until you see a black "+"....then double click and the formua should be populated as far down column D as you have data in column C
regards, barry

Thanks that worked.
I have two files with these two opposing date formats. how do i do this procedure in reverse, if for example I want the
14/01/1998 date to become 19980114

that is the basically way you format your cell, select the cell-->right click-->format cell-->date--> and choose the option of dd/mm/yyyy
and hit ok and your dates will come in that format.
Saurabh...

That just fills the cells with endless #######
I double clicked on a cell and e.g. 19951121 is still undernith the ###
When i click away then the ##### come back

That is because you need to increase the column width, To increase the column width, select the whole column and double click over it and it will increase the column width appropiately.
Saurabh...

Sounds like the column isn't wide enoogh to display the data. Either widen the column or reduce the font size
barry

I tried that as i saw that problem before but it is not that this time.
Not sure what caused it, anyhow I reopened the file and it does not happen the same way but still does not process the data.
In fact there is no option to make it go back to 19980108 for example.

I.E The reverse. 08/01/1998 > 19980108

08/01/1988
08/02/1988
08/03/1988
08/04/1988
08/05/1988
08/08/1988
08/09/1988
08/10/1988
08/11/1988
08/12/1988
08/15/1988
08/16/1988
08/17/1988
08/18/1988
08/19/1988
08/22/1988
08/23/1988
08/24/1988
08/25/1988
08/26/1988
08/29/1988
08/30/1988
08/31/1988
09/01/1988
09/02/1988
09/05/1988
09/06/1988
09/07/1988
09/08/1988
09/09/1988
09/12/1988
09/13/1988

Select allOpen in new window

Can you please upload your sample file so that we can have a look...
Saurabh...

Its just a CSV with a bunch of dates in it, I attached it anyhow
dates.csv

Can you give me your data before you do text to columns over it.

Yes this is my data. As I stated a few comments before: how do you do the reverse and go from a column of e.g. 14/08/1998 to 19980814
As I have two data files and in some instances I would like to do this reverse conversion to the one we talked about at the begining of the thread.

There you go, I formated your column in DD/MM/YYYY format which you are looking for, which does what you are looking for.
Saurabh...

dates.csv

Saurabh,

Thanks for your attempted help, however I dont think you correctly read my two comments above:


> Thanks that worked.
> I have two files with these two opposing date formats. how do i do this procedure in reverse, if for example I
> want the 14/01/1998 date to become 1998014

and

>As I stated a few comments before: how do you do the reverse and go from a column of e.g. 14/08/1998
> to19980814

I want to do the reverse as well.... going from the slash, slash, 14/08/1998 to 19980814.
Can you please tell me how to do that in excel.

C

thanks again