Change date automatic in an excel column

[dahlgrenen]

I have a column with dates in format yyyy/mm/dd
I copy dates from other excelfiles that has the date format yyyy-mm
When I copy the dates with the DATE format yyyy-mm, they keep that format even if the new column is set to DATE format as yyyy/mm/dd.

I would like the dates with the format yyyy-mm to automatic change to yyyy/mm/dd when I paste them in my column. I don't want to paste them as special past.

Please if anyone have some ab code that could help me with this do tell me and were I chould save it. I have already tried many ways but does not get it to work, and I can't download any converter to do the job for me. So Thank you for helping.​

 

[GaryWMn]

You could use Data>Text to Columns to convert these values to dates. Select your column of values in the YYYY-MM format. Select Data > Text to Columns. Choose either Delimited or Fixed Width. Click Next until you get to the Column Data Format and choose Date: YMD. When you click Finish, your values should be converted to dates.

Let us know if this works for you.

Gary

 

[dahlgrenen]

It changes the format perfecly!
Now the only problem is that the date changes as well, to 1905/7/2. From the beginning the date was for exempel "2011-05"

 

[dahlgrenen]

Oh, after just trying some more I get it to work as it should.
Thank you very much!

 

[dahlgrenen]

Now I just wonder if I could do it automtic when the dates is past?
because know I must do all that steps that you wrote after every time I pasted new dates.
I treied to record a macro of it, but it only sets the dates I have already copied to the right format, it can't changes any dates that I have paste after that I have racorded the macro.
Do you have any thought?