Text values that look like dates but are stored as text are one of the most common data quality issues in Excel. They cannot be sorted chronologically, used in date calculations, or formatted as dates. This guide covers every method to convert text to proper Excel dates.
Why Excel Stores Dates as Text
This happens when dates are imported from external sources — CSV files, database exports, copy-pasting from web pages — where the date format does not match Excel’s regional date settings. Excel treats unrecognized date formats as text strings rather than dates.
Method 1: DATEVALUE Function
=DATEVALUE(A2)DATEVALUE converts a text string that looks like a date into an Excel date serial number. Format the result cell as Date to display it properly. This works for standard date formats like “January 15, 2024”, “15/01/2024”, and “2024-01-15”.
Method 2: Text to Columns
Select the column with text dates. Go to Data → Text to Columns. Click Next twice to reach Step 3. Under Column Data Format, select Date and choose the date order (MDY, DMY, or YMD) matching your text format. Click Finish. Excel converts the text values to proper dates.
Method 3: Find and Replace
Sometimes a simple Find and Replace triggers Excel to re-evaluate text as dates. With your text dates selected, press Ctrl+H, search for “/” and replace with “/” (same character). Excel re-parses each cell after the replacement and may recognize the date format.
Frequently Asked Questions
How do I convert ISO date format (2024-01-15) to an Excel date?
Use: =DATE(LEFT(A2,4), MID(A2,6,2), RIGHT(A2,2)). This extracts the year, month, and day from the ISO string and creates a proper Excel date. Format the result cell as Date.