TedJordan.Org - Logo
Text-to-Column-to-format-dates-Excel

Table of Contents

Share this article

Join our newsletter

How to convert text to date with Text to Columns in Excel?

Become an expert in MS Excel Spreadsheets by following our various tutorials. Save time and become the new SME for your team. Join our Excel Qualifying Course now!

Have you ever had issues when trying to sort out dates in your spreadsheets? It often happens when dates are written in different formats, like text. The solution is to convert text to date.

Discover an easy way to fix this in Excel by using the Text to Columns Wizard tool!

When we import data from other files, dates can sometimes be imported as text instead of dates. This is causing a date formatting problem in Excel.

>> Did you know cell values aligned left by default are text and those aligned right are dates and number?

In MS Excel, you can quickly change a date written as text to a date written in a date format.
Use the Text to Columns tool by following these quick 5 steps:

Select all the dates.
Go to Data and click on Text to Columns.

– Once it’s done, the Text to Columns Wizard pop-up window will open; by default “Delimited” is selected. Click Next.

By default, “Tab” is selected as delimiter. Click Next.

For the last step, select “Date” instead of “General” and select the date format you want: DMY (Day, Month, Year), MDY or else. Click Finish.

Now all the dates are in the correct format!

All dates are in date format in Excel.

You notice that, in our example, dates are not following the same date format: the 1st row uses DD/MM/YY and the other ones follow DD/MM/YYYY.

Unifying date formats in Excel is extremely quick!

Select all the dates and press Control+1 on your keyboard: the Format Cells window will pop up.

Go to Date, select the format you want to use and click OK.

And voilà !

All dates respect the same format date: DD/MM/YY in Excel.

If someone asks you how to use Text to Columns to convert dates, now you know!