Dates stored as text cannot be sorted chronologically, used in date arithmetic, or formatted as dates. This is one of the most common Excel data quality problems, caused by importing from external sources where the date format does not match Excel settings.

Identify Text Dates vs Real Dates

Text dates are left-aligned in cells; real dates are right-aligned. Select a cell and try applying a Date format — real dates update their display; text dates do not change. A green triangle in the corner of the cell also indicates a number or date stored as text.

Method 1: DATEVALUE Function

=DATEVALUE(A2)

Format the result cell as a Date to display it correctly. DATEVALUE works for most standard date text formats that match your system regional settings, including formats like “15/01/2024”, “January 15, 2024”, and “2024-01-15”.

Method 2: DATE Function for Non-Standard Formats

=DATE(LEFT(A2,4), MID(A2,6,2), RIGHT(A2,2))

Use this formula for ISO format dates like 2024-01-15. For DDMMYYYY format: =DATE(RIGHT(A2,4), MID(A2,3,2), LEFT(A2,2)). This extracts year, month, and day using text functions then constructs a proper Excel date.

Method 3: Text to Columns

Select the column with text dates. Go to Data, then Text to Columns. Click Next twice to reach Step 3. Under Column Data Format, select Date and choose the order (MDY, DMY, or YMD) matching your text format. Click Finish to convert the entire column at once.

Frequently Asked Questions

Why does DATEVALUE return a VALUE error?

DATEVALUE returns #VALUE! when the text string does not match a recognized date format for your regional settings. If your system uses DMY format but the text is in MDY format, DATEVALUE cannot parse it. Use the DATE function with LEFT, MID, and RIGHT to handle non-standard formats manually.

How do I fix text dates imported from a CSV file?

The fastest fix is Text to Columns — select the column, go to Data, Text to Columns, click Next twice, select Date format with the correct order, and click Finish. For large datasets or repeated imports, Power Query handles date parsing with explicit format settings that persist across refreshes.