How to Automatically Convert Text to Dates Using Excel Power Query

When you import raw data into Microsoft Excel from a third-party CRM system, a database export, or a poorly formatted CSV file, dates are frequently misinterpreted as generic text strings (e.g., “20231024” instead of “10/24/2023”). If Excel registers a date as text, you cannot use chronological sorting, filtering, or date-based formulas. Rather than manually typing out thousands of corrections using the “Text to Columns” wizard every time you download a new report, you can use Excel Power Query to automatically convert these text strings into properly formatted dates.

How to Import Data into Power Query

Power Query acts as a middleman between your raw data file and your final spreadsheet, allowing you to build a reusable set of transformation rules.

1. Open a blank Microsoft Excel workbook.
2. Click on the Data tab in the ribbon.
3. Click Get Data > From File > From Text/CSV (or choose the appropriate source for your raw data).
4. Locate your raw data file and click Import.
5. When the preview window appears, do not click Load. Instead, click Transform Data.

This will open the separate Power Query Editor window, displaying your data in a grid.

How to Convert Text to Dates

Now we will instruct Power Query how to interpret the broken text column.

1. Locate the column containing your broken text dates.
2. Look at the small icon on the left side of the column header (it likely shows ABC, indicating Power Query currently reads it as text).
3. Right-click the column header.
4. Hover over Change Type in the context menu.
5. Select Date.

If your text dates were in a somewhat standard format (like “Oct 24 2023”), Power Query will instantly convert them into proper date values (the icon in the header will change to a small calendar).

How to Handle Complex Text Formats

If your text was a single unbroken string (like “20231024”), clicking ‘Change Type’ will likely result in an “Error” message. To fix this, we must use a custom extraction.

1. Delete the failed step by clicking the red ‘X’ next to “Changed Type” in the Applied Steps panel on the right.
2. Select the text column.
3. Click the Add Column tab at the top of the window, then click Custom Column.
4. Name the column “Corrected Date”.
5. In the custom column formula box, type: Date.FromText([YourColumnName]) (Replace ‘YourColumnName’ with the actual name of your column).
6. Click OK.

How to Automate Future Imports

Once your dates look correct, click the Home tab in Power Query and select Close & Load. The cleaned data will populate your Excel spreadsheet.

Because Power Query saves your steps, you never have to do this again. The next time you receive a new raw data file, simply save it over the old file (using the exact same file name and location), open your Excel workbook, click the Data tab, and click Refresh All. Power Query will automatically suck in the new text data, convert the dates, and update your spreadsheet instantly.