Excel convert column with Date and Text to Date

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

text_to_date

2) Paste the copied Range to Notepad

text_to_date_02

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.

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *