[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

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


I have a column of dates, sample below. I want to convert them from 19981014 to be 14/10/1998.
Notice that the weekends are missing in the sequence, this needs to be maintained.
I tried the old select and drag but it just included the weekends, which ic not what i want
How can i convert this column in Excel?

C



19981006
19981007
19981008
19981009
19981012
19981013
19981014
19981015
19981016
0
cakester
Asked:
cakester
  • 9
  • 9
  • 2
  • +1
1 Solution
 
Saurabh Singh TeotiaCommented:
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...
0
 
TracyVBA DeveloperCommented:
you can also do it by a formula like so:

=RIGHT(A1,2)&"/"&MID(A1,5,2)&"/"&LEFT(A1,4)
0
 
cakesterAuthor Commented:

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.

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Saurabh Singh TeotiaCommented:
Did you selected the whole column data before going to text to columns as that will change your data in 1 go...
Saurabh...
0
 
cakesterAuthor Commented:

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.

0
 
Saurabh Singh TeotiaCommented:
Don't select DMY it should be YMD and select the date option in the last step.
Saurabh..
0
 
barry houdiniCommented:
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
0
 
cakesterAuthor Commented:


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
0
 
Saurabh Singh TeotiaCommented:
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...
0
 
cakesterAuthor Commented:

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
0
 
Saurabh Singh TeotiaCommented:
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...
0
 
barry houdiniCommented:
Sounds like the column isn't wide enoogh to display the data. Either widen the column or reduce the font size
barry
0
 
cakesterAuthor Commented:

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

Open in new window

0
 
Saurabh Singh TeotiaCommented:
Can you please upload your sample file so that we can have a look...
Saurabh...
0
 
cakesterAuthor Commented:

Its just a CSV with a bunch of dates in it, I attached it anyhow
dates.csv
0
 
Saurabh Singh TeotiaCommented:
Can you give me your data before you do text to columns over it.
0
 
cakesterAuthor Commented:




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.
0
 
Saurabh Singh TeotiaCommented:
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
0
 
cakesterAuthor Commented:


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


0
 
Saurabh Singh TeotiaCommented:
To go to reverse use this formula in B Column...
=TEXT(A1,"yyyymmdd")
0
 
cakesterAuthor Commented:
thanks again
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 9
  • 9
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now