This Excel tutorial explains how to convert a column with Date and Text to Date.
Excel convert column with Date and Text to Date
Excel has a built-in Function DateValue, which converts a Date in Text format to date serial. What if the column contains both Text and Date? If you use DateValue Function on Date, #VALUE! will return. So how can we convert Text and Date at the same time?
Convert Text to Date in Notepad
The below method is even faster than DateValue Function or Text to Column, it is applicable to a column with pure Text or a column mixed with Text and Date.
1) First of all, copy the column Range that contain Text. In the below example, copy Range A1:A2
2) Paste the copied Range to Notepad
You can see Notepad disregards the underlying format in Excel, it will paste whatever it looks. Therefore you should note carefully the month and date format.
3) Finally copy and paste back to Excel.
Whether the value pasted to Excel is mm/dd or dd/mm depends on the Local Time setting in Control Panel.
