How to Convert Text to Date in Excel

When you import data into Excel, it may come in many different forms that Excel doesn’t recognize. Sometimes when dates are imported from an external source, they will come in formatted as text.

It’s also important to know that Excel applies date formats based on your system region settings. So, when you import data into Excel that came from a different country, Excel may not recognize them and store them as text entries.

If you have this issue, there are many ways you can use to convert text to a date in Excel and we will cover them all in this tutorial.

Methods for Converting Text to Date

If your worksheet contains dates formatted as text, instead of actual dates, it’s not possible to use them in any calculations. So, you need to convert them back to actual dates.

If you see your dates aligned to the left that means they are formatted as text because texts are left-aligned by default. And the number and dates are always aligned to right.

There are few different ways you can convert text to a date in Excel, they are:

Error Checking option
Excel Text to Columns feature
Find & Replace
Paste Special tool
Excel Formula and functions

Convert Text to Date Using Error Checking Option

Excel has an in-built error checking feature that spots some obvious errors in your data. If it finds any error, it will show you a small green triangle (an error indicator) in the upper-left corner of the cell, which has the error. If you select that cell, a caution sign with a yellow exclamation point will pop up. When you move your cursor over that sign, and Excel will inform you about the possible issue with that cell.

For example, when you enter the year in the two-digit format in your date, Excel assumes that date as a text and stores it as text. And if you select that cell, an exclamation mark will appear with a warning: ‘This cell contains a date string represented with only 2-digits of the year’.

If your cells show that error indicator, click on the exclamation mark, and it will display a few options to convert dates formatted as text into actual dates. Excel will show you options to convert it to 19XX or 20XX (19XX for 1915, 20XX for 2015). Select the appropriate option.

Then, the text will be converted into proper date format.

How to Enable Error Checking Option in Excel

Usually, the Error Checking option is turned on in Excel by default. If the error checking feature doesn’t work for you, you need to enable the Error Checking in Excel.

To do this, click on the ‘File’ tab’ and select ‘Options’ in the left panel.

On the Excel Options window, click ‘Formulas’ in the left panel, and in the right-side panel, enable ‘Enable background error checking’ under the Error Checking section. And tick the ‘Cells containing years represented as 2 digits’ within the Error-checking rules section.

Convert Text to Date Using Excel Text to Column Feature

Text to Column is a great feature in Excel that allows you to split data into multiple columns, and also it is a powerful tool for converting text values to date values. This method recognizes several different data formats and converts them into proper date format.

Converting Simple Text Strings to Dates

Let’s say your dates are formatted in text strings like this:

You can use Text to Columns wizard to quickly reformat them all back to dates.

First, select the range of text entries you want to convert to dates. Then, go to the ‘Data’ tab in the Ribbon and click the ‘Text to Columns’ option in the Data Tools group.

The Text to Columns wizard will appear. On step 1 of the Text to column wizard, choose the ‘Delimited’ option under Original data type and click ‘Next’.

In step 2, uncheck all ‘Delimiters’ boxes and click Next.

In the final step of the wizard, select ‘Date’ under Column data format and choose your date format from the drop-down list next ‘Dates’, and click the ‘Finish’ button. In our case, we are converting the text dates represented as “01 02 1995” (day month year), so we choose ‘DMY’ from the ‘Date:’ drop-down list.

Now, Excel converts your text dates to actual dates and displays them right-aligned in the cells.

Note: Before you start using Text to Column feature, make sure all your text strings are in an identical format, otherwise texts won’t be converted. For instance, if some of your text dates are formatted like month/day/year (MDY) format while others are day/month/year (DMY), and when you choose ‘DMY’ in step 3, you would get incorrect results. Like it is shown in the below picture.

Converting Complex Text string to Date

Text to Columns feature comes in handy when you want to convert complex text strings to dates. It allows you to use the delimiters to identify where your data should be split and displayed across 2 or more columns. And combine the split parts of dates into whole date using DATE function.

For example, if your dates are displayed in multi-part text strings, like this:

Wednesday, February 01, 2020
February 01, 2020, 4.10 PM

You can use the Text to column wizard to separate the day, date, and time information that is delimited by a comma, and display them across multiple columns.

First, select all text strings that you want to convert to dates. Click the ‘Text to Columns’ button on the ‘Data’ tab. On step 1 of the Text to column wizard, choose the ‘Delimited’ option under Original data type and click ‘Next’.

In step 2 of the wizard, select the delimiters your text strings contain and click ‘Next’. Our example text strings separated by comma and space – “Monday, February 01, 2015, 1:00 PM”. We should choose ‘Comma’ and ‘spaces’ as delimiters to split the text strings into multiple columns.

In the final step, select the ‘General’ format for all columns in the Data Preview section. Specify where the columns should be inserted in the ‘Destination’ field, if you don’t, it will overwrite the original data. If you want to ignore some part of the original data, click on it in the Data Preview section and select the ‘Do not import column (skip)’ option. Then, click ‘Finish’.

Now, the parts of dates(days of week, month, year, time) are split in columns B, C, D, E, F, and G.

Then, combine the date parts together with the help of a DATE formula to get the whole date.

The syntax of the Excel DATE function:

=DATE(year,month,day)

In our example, the month, day, and year parts are in columns C, D, and E respectively.

The DATE function only recognizes numbers not text. Since our month values in column C are all text strings, we need to convert them to numbers. To do that, you need to use the MONTH function to change the name of the month to the number of the month.

To convert a month’s name to a month’s number, use this MONTH function inside the DATE function:

=MONTH(1&C1)

The MONTH function adds 1 to cell C2 which contains the name of the month to convert the month name to the corresponding month number.

This is the DATE function we need to use to combine date parts from different columns:

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

Now use the fill handle at the bottom corner of the formula cell and apply the formula to the column.

Convert Text to Date Using Find and Replace Method

This method uses delimiters to change the format of text to dates. If day, month, and year in your dates are separated by some delimiter other than a dash (-) or slash (/), Excel will not recognize them as dates and will go ahead and store them as text.

To fix this issue, use the Find and Replace feature. By changing the non-standard period delimiters (.) with slashes (/) or dash (-), Excel will automatically change the values to dates.

First, select all the text dates you want to convert to dates. On the ‘Home’ tab, click the ‘Find & Select’ button at the right-most corner of the Ribbon and select ‘Replace’. Alternatively, press Ctrl+H to open the Find and Replace dialog box.

In the Find and Replace dialog box, type the delimiter that your text contains (in our case full stop (.) in the ‘Find what’ field and a forward slash (/) or dash (-) in the ‘Replace with’ field. Click the ‘Replace All’ button to replace the delimiters and click ‘Close’ to close the window.

Now, Excel recognizes that your text strings are now dates and it automatically formats them as dates. Your dates will be aligned right as shown below.

Convert Text to Date Using Paste Special Tool

Another quick and easy way to convert text strings to dates is by adding 0 to the text string using paste special option. Adding zero to value converts the text to the date’s serial number which you can format as a date.

First, select an empty cell and copy it (select it and press Ctrl + C to copy).

Then, select the cells containing the text dates you want to convert, right-click and select the ‘Paste Special’ option.

In the Paste Special dialog, select ‘All’ under the Paste section, choose ‘Add’ under the Operation section, and click ‘OK’.

You can also perform other arithmetic operations subtract/multiply/divide the value in the destination cell with the pasted value (like multiply cells with 1 or divide by 1 or subtract zero).

When you select ‘Add’ in Operation, it adds ‘zero’ to all selected text dates, since adding a ‘0’ doesn’t change the values, you’ll get the serial number for each date. Now all you got to do is change the format of the cells.

Select the serial numbers and on the ‘Home’ tab, click on the ‘Number Format’ drop-down list in the Number group. Select, ‘Short Date’ option from the drop-down.

As you can see now the numbers are formatted as dates and aligned right.

Convert Text to Date Using Formulas

There are two functions primarily used to convert text to date: DATEVALUE and VALUE.

Using Excel DATEVALUE Function

The Excel DATEVALUE function is one of the easiest ways to convert a date represented as text into a date’s serial number.

The syntax of DATEVALUE function:

=DATEVALUE(date_text)

Argument: date_text specifies the text string you want to covert or reference to the cell containing text dates.

The formula:

=DATEVALUE(A1)

The following picture illustrates how the DATEVALUE function handles a few different date formats which are stored as text.

You got the date serial numbers, now you need to apply the Date format to these numbers. To do that, select the cells with serial numbers, then go to the ‘Home’ tab and choose ‘Short Date’ from the ‘Number Format’ drop-down list.

Now you have your formatted dates in column C.

Even if there is no year part in your text date (A8), DATEVALUE will use the current year from your computer’s clock.

The DATEVALUE function will only convert the text values that look like a date. It can’t convert a text that resembles a number to date, nor can it change a number value to date, for that you’ll need Excel’s VALUE function.

Using Excel VALUE Function

The Excel VALUE function is able to convert any text string that resembles a date or number into a numeric value, so it’s really helpful when it comes to converting any number, not just dates.

The VALUE function:

=VALUE(text)

text– the text string we want to convert or reference to the cell containing the text string.

The example formula to convert the text date:

=VALUE(A1)

The below VALUE formula can change any text strings that look like a date into a number as shown below.

However, the VALUE function doesn’t support all types of date values. For instance, if dates use decimal places (A11), it will return the #VALUE! error.

Once you have the serial number for your date, you will need to format the cell with the date serial number to make it look like a date as we have done for the DATEVALUE function. To do that, select the serial numbers and select the ‘Date’ option from the ‘Number Format’ drop-down menu in the ‘Home’ tab.