How to Convert Text to Date in Excel — 4 Methods

Dates imported from CSV files, databases, or copied from websites often arrive in Excel as text — meaning you cannot sort, filter, or calculate with them as real dates. Here are the best methods to convert text to proper Excel dates.

Method 1: DATEVALUE Function (Most Flexible)

The DATEVALUE function converts a date stored as text into an Excel date serial number. It works with most recognisable date formats.

1. In an empty cell next to your text date (e.g., A1 contains "23 June 2026"), enter:
   =DATEVALUE(A1)
2. Press Enter — you get a number (e.g., 46195). This is Excel's date serial number.
3. With the cell selected, press Ctrl+1 to open Format Cells.
4. Select Date and choose your preferred format. The cell now shows a proper date.
5. Copy the helper column, paste as Values Only (Ctrl+Shift+V → Values), then delete the original text column.

Common DATEVALUE formats it recognises: "23 June 2026", "June 23, 2026", "23-Jun-2026", "2026-06-23"

Method 2: Text to Columns (Fastest for Bulk Conversion)

This method works when all your text dates are in a consistent format that Excel can recognise (like YYYY-MM-DD or MM/DD/YYYY).

1. Select the column of text dates.
2. Go to Data → Text to Columns.
3. Click Next twice (skip delimiters — we are not splitting columns).
4. In Step 3, under Column data format, select Date and choose the date order (MDY, DMY, or YMD) that matches your text.
5. Click Finish. Excel converts all the text in-place to real dates.

Format the column as Date (Ctrl+1 → Date) if it still shows numbers.

Method 3: Find & Replace to Fix Separators

If your dates use a separator Excel does not recognise in your locale (e.g., dashes where it expects slashes), a quick Find & Replace fixes them:

1. Select the column with text dates.
2. Press Ctrl+H to open Find & Replace.
3. In Find what, type the current separator (e.g., a dot . or dash -).
4. In Replace with, type the separator your locale uses (e.g., /).
5. Click Replace All. Excel should now recognise the dates automatically.

Method 4: DATE Function (For Split Date Parts)

If the year, month, and day are in separate columns (e.g., A1=2026, B1=6, C1=23), use:

=DATE(A1, B1, C1)

This always returns a proper Excel date. Format the result as Date.

Method 5: Power Query (Best for Recurring Imports)

For data that arrives regularly from an external source, Power Query is the most robust solution:

1. Select your data → go to Data → From Table/Range.
2. In Power Query, right-click the date column header → Change Type → Date.
3. If it fails, right-click → Change Type → Using Locale → select the locale that matches your date format.
4. Click Close & Load. Power Query converts dates every time you refresh.

Why Are My Dates Still Showing as Text?

• Green triangle in cell corner: Excel suspects the value is a number stored as text. Click the warning icon → "Convert to Number."
• Left-aligned dates: Dates align right by default in Excel. If your date is left-aligned, it is still text.
• TRIM first: Leading or trailing spaces break DATEVALUE. Use =DATEVALUE(TRIM(A1)).