Change column type to date

Is there a way to change the column type to "Date". I have changed the format to "date" but it doesn't work.

I need to manually go to each cell  and press F2 to edit content and then press enter to change it to date format. Just wonder whether a quick way to do this ?

Tks
MyTest.xlsx
Step1onRow29.png
Step2onRow29.png
AXISHKAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveCommented:
Changing the column to data is a good start, then...
Two issues...

No 1... use Find and replace with 未齐
Press [ctrl]+[f] to open dialog box.
paste the 未齐 into the find.
click 'replace all'

No 2... highlight column 'B' and 'Paste Special' add values.
Go to the bootom of your data.
Click in cell in column 'B' (B4117)
Press [ctrl]+[shift]+[up]
Press [ctrl]+[c] to copy
higlight data in column 'A'
Right click, Paste Special
Select 'Data' and 'Add'
Click 'OK'

This should sort the bulk of your issues
ATB
Steve.
0
AXISHKAuthor Commented:
User want to keep the words, only translate the text field data to actual "date" field. Any alternative solution ? Tks
0
AXISHKAuthor Commented:
No sure option 2 can fix my problem but I can't find the "Data and Add". Attach my screen for your reference.
No2.png
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Glenn RayExcel VBA DeveloperCommented:
I'll re-state Steve's 2nd instruction (after you've removed the characters from step one).
1) Click on cell B2
2) Press [Shift]+[Ctrl]+[End].
This will select B2:B4117
3) Press [Ctrl]+[C] (copy)
4) Click on cell A2
5) Click on Menu: Home tab, Clipboard section, Paste, PasteSpecial on the bottom
paste special: add function6) Click OK
This will convert all cells to internal date values (integers, but not formatted as dates)
7) Select A2:A4117
8) Re-format the cells as you wish (dates with names for months).

Regards,
-Glenn
0
AXISHKAuthor Commented:
The Chinese char need to keep for recording purpose. Only change the date field (without text) to pure date field.

Tks
0
Rory ArchibaldCommented:
Use Data-Text to Columns, specify delimited, clear all the options in step 2, then in step 3 specify Date and choose the format you have (DMY).
0
AXISHKAuthor Commented:
Sorry, can't get your steps. can you assist with screen dump, Tks
0
Rory ArchibaldCommented:
Here are the three steps.
TTC-step-1.PNG
TTC-step-2.PNG
TTC-step-3.PNG
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.