There are many ways to convert dates formatted as text into actual dates in Excel and this tutorial aims to explore them all.
When you import data into Excel, it may come in many different forms that Excel doesn’t recognize. When that happens, chances are the dates will come in formatted as text. Even though they look like dates, but don’t behave like dates.
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’re faced with this problem, 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 spreadsheet contains dates formatted as text, rather than actual dates, you cannot use them for any calculation purpose. So, we need to convert them back to dates.
You can often see if a date is incorrectly formatted as text because it is aligned to the left by default. And the number and dates are right-aligned by default.
There are few different methods that you can use to convert text to a date in Excel:
- 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. When this happens, you will see a small green triangle (an error indicator) in the upper-left corner of the cell and when you select the cell, a caution sign with a yellow exclamation point will appear. Hover over that sign, and Excel will inform you about the potential issue.
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 display this error indicator, click on the exclamation mark, and it will display a few options to convert text representations of dates into actual dates. Excel will ask if you want 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 enabled in Excel by default. If the above method 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 check ‘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 column of text entries you want to convert to dates. Switch to the ‘Data’ tab in the Ribbon and click the ‘Text to Columns’ option in the Data Tools group.
The Text to Columns wizard appears. 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 formatted as “01 02 1995” (day month year), so we select ‘DMY’ from the drop-down box.
Now, Excel converts your text strings to dates and displays right-aligned in the cells.
Note: When using the Text to Column method, all of your text strings should be formatted identically. 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 features come 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. For that, you’ll have to use both the Text to Columns wizard and Excel 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 to be converted 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’.
The following picture shows the result, with the original data in column A and the split data in columns B, C, D, E, F, and G.
Finally, you have to combine the date parts together by using a DATE formula.
The syntax of the Excel DATE function is self-explanatory:
The DATE function is self-explanatory. In our case, the year is in column E, the day is in column D, and the month in column C.
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 Excel MONTH function to change a month’s name to a month’s number.
To change a month’s name to a month’s number, use this MONTH function inside the DATE function:
The above function concatenate 1 with cell C2 which contains the name of the month so that the MONTH function can convert it to the corresponding month number.
This is the DATE function we need to use to combine date parts from different columns:
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.
However, you can fix this with the Find and Replace tool. By replacing the non-standard delimiters with slashes (/) or dash (-), Excel will automatically identify the values as 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 should detect that your text is a date and format it automatically as a date. Your dates will be aligned right as shown below.
Convert Text to Date Using Paste Special Tool
Another way to convert text to dates is by adding zero to the text paste special method. 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 box, 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, Excel adds zero to all cell values, since adding a zero does not change the value, you get the date’s serial number. 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 DATEVALUE function is a basic method used to convert a date in the text format to an Excel date serial number.
The syntax of Excel’s DATEVALUE:
date_text is where you use text value you want to covert or reference to the cell with a date stored as a text string.
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.
If you omit the year in your text date (A8), DATEVALUE will pick the current year from your computer’s clock.
The DATEVALUE function will only convert the text values that look like a date. It cannot convert a text that looks like a number to date, nor can it a numeric value to date, for that you will need to use the Excel VALUE function.
Using Excel VALUE Function
The Excel VALUE function can convert any text string that looks like a date or number into a number, so it’s useful for fixing any number, not just dates.
The syntax of the VALUE function:
text is the text string we want to convert or reference to the cell containing the date text string.
An example formula:
The VALUE function can process any text string that looks like a date or number 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.
So, there you have 5 different ways to convert dates formatted as text in Excel.