Solved Change date automatic in an excel column

April 22, 2012 at 07:15:49
Specs: Windows XP
I have a column with dates in format yyyy/mm/dd
I copy dates from other files that has the date format yyyy-mm
I would like the dates with the format yyyy-mm to change to yyyy/mm/dd when I paste them in my column. I have already set the format in that colum to be yyyy/mm/dd, the way I want it but still the dates I copy does not changes their format.

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.


See More: Change date automatic in an excel column

Report •


#1
April 22, 2012 at 08:57:34
I copy dates from other files that has the date format yyyy-mm

Is the other file an Excel file?
After you have copied it, does it left justify in the cell?
If you apply a Custom Format of General does it change into a number?

MIKE

http://www.skeptic.com/


Report •

#2
April 22, 2012 at 17:35:04
It'a an excel file. When I have copied the text is plaaced to the left side of the cell. If I have a gengeral or custom format it does not change unless I click in the cell and then press enter,but I can't do that with all dates. I'm not sure I understood correctly what a "Custom Format of General" is.

Report •

#3
April 22, 2012 at 18:09:11
✔ Best Answer
If it is left justified in the cell and does not change into a number when formatted as General, then it is seen by Excel as TEXT, not as a DATE.

So what you have is a Text string that looks like: 2010-12

To convert this Text string into a usable date you can do something like:

With your Text string of 2010-12 in Cell A1

in Cell B1, first format as Date: YYYY/MM/DD

then enter the formula:

=DATEVALUE(LEFT(A1,4)&"/"&RIGHT(A1,2)&"/"&1)

Since you only have the Month & Year, the above formula defaults to
the first Day of the month.

So you get a Real Date of 2010/12/01, the first day of the month.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
April 22, 2012 at 18:30:51
I tried that just now and got the error "#value"

Report •

#5
April 22, 2012 at 18:38:14
I think they are formated as dates anyway because is says "date" on the ribbon. In that box in the category "number". And if I press in the cell with the date it change to the format I want. text does not seem to do that?

Report •

#6
April 22, 2012 at 18:39:03
Is your Text string: 2010-12
With four characters before the dash
and two characters after the dash?

If not you will get the #Value error.

Here is a modified version that should work in all cases
where there are four character before the dash
and one or two after the dash:

=DATEVALUE(LEFT(A1,4)&"/"&RIGHT(A1,LEN(A1)-FIND("-",A1))&"/"&1)

MIKE

http://www.skeptic.com/


Report •

#7
April 22, 2012 at 18:44:32
Select a cell that has your YYYY-MM string in it.
Next, select Format from the Ribbon
At the bottom of the drop down
Select Format Cells
Select Number

If the cell does not display as a 5 digit number, then it is not a Date.

MIKE

http://www.skeptic.com/


Report •

#8
April 22, 2012 at 18:54:18
It does not change.
But if I copy a blank cell, and past it on the cell with the date, as "special" then "add", then I get the 5 digital value.

Report •

#9
April 22, 2012 at 18:58:08
Still, If they are formated astext, I don't get the code you gave me to work, I get the error "#value"

Report •

#10
April 22, 2012 at 19:03:18
Now I get it to work! but if I change the format of the dates to text before I apply your formula it workes perfect. But as I thought they are not formated as text. So If I use your formula without changeing their format, I get the 5 digital in the cell I you the formula in.

Report •

#11
April 22, 2012 at 19:06:46
When you select your YYYY-MM cell,
what do you see in the formula bar at the top?

MIKE

http://www.skeptic.com/


Report •

#12
April 22, 2012 at 19:12:49
it says "2010-03"
If I press it, click somewere else and then press the formula bar agian it have change to "2010/3/1"

Report •

#13
April 22, 2012 at 19:14:11
If you see a regular date in the formula bar,
then all you need do is reformat the cell
to display the date as you wish.

Select the cell,
On the Ribbon
Select Format
At the bottom of the drop down
Select Format Cells
Select Custom Format

In the input box just below the word Type:
delete what ever is in the input box,
and enter the format: yyyy/mm/dd

MIKE

http://www.skeptic.com/


Report •

#14
April 22, 2012 at 19:27:05
No it does not work that way. I know have to change the format in the whole column thogh, I must do as I written before, copy a blank cell then past as special and then click "add", but the thing is that I whant that to happen atomatic when I copy the dates to the new file. I thought that maby there are some vb code.

But anyway, Thankyou very much


Report •

#15
April 22, 2012 at 19:29:07
I think what is happening is when you copy the data over from the other sheet,
you also copy the cells original format, which overwrites the format in your sheet.

So you need to copy the data, then instead of just a regular paste
do a paste special, values.

MIKE

http://www.skeptic.com/


Report •

#16
April 22, 2012 at 19:41:44
Yes I thought so. I have alredy figured out have to change the formats.
But not how to do that automatic, I want all the dates in that column to change direcly when I paste them in my file.

Report •

#17
April 22, 2012 at 19:56:43
Because when I copy the dates I also Copy a lot of text, And Ido this many times. There for I need a formula, lika vb code.

Report •


Ask Question