Date Format will not change

[lilly83]

I am using excel 2013 and I have donwloaded an excel file which has a date column but some of the dates wont change format to date format.
See example below
Purchase Date
01-03-13
01-04-13
01-08-13
01-09-13
01-10-13
1/14/13
01-11-13


The second last one from the bottom 1/14/13 will not chnage to look like the othesrs and thefore I cannot pick it up in the pivot table. I have tried to use text to columns whihc normally works but will not in this case

 

[LaurieHamlin]

First use format([field], "mm-dd-yy").

 

[SkipVought]

hi,

1) Change the formatting of the column to a DATE Number format.

2) COPY the column

3) PASTE into the column at ROW 1.

Please note that simply changing a Number Format changes NOTHING in the underlying value. Initially, it seems from your symptoms, the UNDERLYING VALUE for you column of values was
[pre]
Purchase Date
41277
41278
41282
41283
41284
1/14/13
41285
[/pre]
The 1/14/13 never got converted to a Date Serial Value. By FIRST changing the Number Format and then reentering the value (via PASTE) you will coerce the converstion for a string to a Date Seril Number.

faq68-5827

 

[lilly83]

I changed the format of the colum to a DATE number format and copied it. Then I copied it and pasted in a new colum from row 1 but it didnt work. It is still in the same format
Purchase Date
41334
41365
41487
41518
41548
1/14/13
41579
41487


The prob is there are 100's of ones like this I need to change.

 

[SkipVought]

Use the build-in feature Data > Text to columns on your column

Select DELIMITED > TAB character (which doe not exist on your sheet

Select DMY and FINISH

 

[lilly83]

Done but it still hasnt changed

 

[SkipVought]

In the Text to columns Wizard, check TAB and SPACE

 

[lilly83]

no luck

 

[SkipVought]

Did you in the Text to columns Wizard STEP 3, select [highlight #FCE94F]Date DMY[/highlight]?

If that did not work, you must have some other character either before or after the 1/14/13.

You can check that using CODE(LEFT(A1,1)) or CODE(RIGHT(A1,1)) using the appropriate cell reference.

The number returned is the ASCII value of the character.

 

[zelgar]

If you have changed the format of the cell from DATE to General or other formats and the contents of the cell are not changing, then the data in the cell is NOT A NUMBER but text. You'll need to change the data in the cell (e.g., delete the data and input it again) so that it's seeing the data as a DATE and not TEXT.

 

[SkipVought]

SkipVought (Programmer)28 May 15 14:48
hi,

1) Change the formatting of the column to a DATE Number format.

 

[billyjoemc]

any luck with this issue. pls follow the process advised

 

[combo]

1/14/13 is a vald date in MDY format, no way to convert this string to date if your computer settings have DMY order. If your computer settings match date order in string, except of formatting you need to re-enetr the cell contents. For big ranges you can copy empty cell, select data range and copy values with subtraction operation.

combo