Excel Text to Date Conversion Guide

[nerdslife_team]

Got text that’s acting like a date in Excel? It’s a common headache when importing data. Excel needs dates in a specific format to play nice with formulas and analysis. Here’s a breakdown of how to get your text dates converted, with some different methods to try.

Method 1: Using the Text to Columns Feature

This is a great way to convert text strings that are consistently formatted into proper dates, especially if you have a large dataset.

Converting simple text strings to dates

Let’s say your dates look like “01 02 1995”, which Excel sees as text. Here’s what to do:

1. Select the cells containing your text dates.

2. Go to the Data tab and click Text to Columns.

3. Choose Delimited in the wizard and click Next.

4. Uncheck any delimiters and click Next.

---

5. In this final step, select Date under “Column data format.” Pick the date format that matches your data (like DMY for “day month year”) then click Finish.

Now, your text dates should be right-aligned, showing they are proper dates!

Note: This works best if all your text dates are in the same format, if some are in DMY and some in MDY you could run into trouble.

Converting complex text strings to dates

Got more complicated text strings, like “Wednesday, February 01, 2020” or “February 01, 2020, 4.10 PM”? Here’s how to handle them using Text to Columns:

1. Select the cells that contain your complex text dates.

2. Go to Data and click on Text to Columns.

3. Choose Delimited and click Next.

4. Select the delimiters present in your text (e.g., comma and space) and then click Next.

5. In this final step, select General as the column data format for all columns. Specify where you want to put the output and click Finish.

Now the text should be split into separate columns.

To get a proper date, use the DATE function along with the MONTH function to convert month names.

For example, if the year is in column E, the month name is in column C, and the day is in column D, the formula is:

=DATE(E1, MONTH(1 & C1), D1)

=DATE(E1, MONTH(1 & C1), D1)

Drag the fill handle to apply the formula to other cells.

---

Method 2: Using the Error Checking Feature

Excel’s error checking is super handy, it can automatically detect when a date is stored as text. A little green triangle shows up on the cell.

1. Click on the cell and a caution icon should appear. Hovering over the icon will give you a message explaining the issue.

2. Choose the option to convert the text into a date.

This is great for simple fixes, like changing “15” to “2015”.

Enabling the error checking option

If you don’t see error checking happening, enable it through:

1. Click File and select Options.

2. Go to the Formulas tab and make sure “Enable background error checking” is checked. Also, check “Cells containing years represented as 2 digits” under “Error checking rules.”

---

Method 3: Using the Paste Special Tool

The paste special trick is a nice way to convert text dates to date serial numbers.

1. Select an empty cell and copy it (Ctrl + C).

2. Select the cells containing your text dates, right-click, and choose Paste Special.

3. In the Paste Special dialog box, select All under Paste and Add under Operation then click OK.

The text dates should be converted to date serial numbers.

4. Select the cells with the serial numbers. Go to Home, click the Number Format dropdown, and select Short Date.

Now, you should have proper dates!

---

Method 4: Using Find and Replace

If your dates use delimiters that Excel doesn’t recognize, such as periods instead of slashes, you can use Find and Replace to change them.

1. Select the cells containing the text dates.

2. Press Ctrl + H to open the Find and Replace dialog box.

3. In Find what, enter the existing delimiter (e.g., “.”). In Replace with, enter a recognized delimiter such as “/”. Click Replace All then Close.

Now, Excel should recognize the dates.

Method 5: Using Formulas

Excel functions like DATEVALUE and VALUE can help to convert text to dates.

Using the DATEVALUE function

The DATEVALUE function converts a date formatted as text into a date serial number.

=DATEVALUE(date_text)

If cell A1 contains a text date, here is the formula:

=DATEVALUE(A1)

Format the cells as dates after applying this.

1. Select the cells with serial numbers, go to the Home tab and then choose Short Date from the Number Format dropdown.

Now you’ll have proper dates.

Using the VALUE Function

The VALUE function converts text strings that look like numbers into numeric values.

=VALUE(text)

To convert a text date in cell A1:

=VALUE(A1)

Format the result as dates using the Number Format options.

By using these techniques, you can get your text dates into proper date formats so that you can analyze your data correctly.