How to Convert Text to Date in Excel?

Calculating the number of days between two dates, sorting and filtering data based on dates, and applying date formatting are some essential operations we frequently do in Excel.

These operations, however, are challenging or impossible to perform if the date information is stored as text strings.

Consequently, date information entered as text strings in Excel must be converted to valid dates if we want to use them in date calculations and other date-related operations.

This tutorial shows nine techniques for converting text to date in Excel. 

Understanding the Concept of Date in Excel

In Excel, dates are serial numbers representing particular days, months, and years since January 1, 1900; for example, the serial number for October 15, 2024, is 45580. 

This number means that 45580 days have passed since January 1, 1990

We can apply different date formats to the numbers representing dates to display them as regular dates in a form understandable to humans. 

Because Excel stores dates as serial numbers, it is possible to perform mathematical operations on dates, such as finding the difference between two dates or adding a certain number of days to a date.

However, when we enter date information in Excel as text, Excel does not recognize it as dates but as text strings which cannot be used in date calculations or other date-related operations.

This tutorial shows how to convert two forms of text strings to dates in Excel:

• Text strings having date formats supported by Excel.
• Text strings having date formats not supported by Excel.

Convert Text to Dates in Excel (for text date format supported by Excel)

Excel supports a variety of date formats that display dates in different ways.

For example, the date format “mm-dd-yyyy” shows the value 44965 as 02-08-2023.

When we enter a date in a date format supported by Excel, Excel recognizes the entry as a date and internally stores the serial number representing the date.

For example, when we enter 02-08-2023, Excel recognizes this value as a date and internally holds its serial number, 44965, while displaying it in the cell as 02-08-2023.

Sometimes, however, date data entered in Excel, even though they may be in date formats supported by Excel, may not be recognized by Excel as valid dates if they are formatted as text.

For example, 02-08-2023 has a date format supported by Excel and looks like a proper date, but if it is formatted as text Excel will not recognize it as a valid date. 

We, therefore, use the methods covered below to convert such date data to valid dates recognized by Excel.

Method #1: Use the DATEVALUE Function to Convert Text to Date in Excel

The DATEVALUE function converts a date from a text string to a number representing the date.

Therefore we can use the function to convert text having date formats supported by Excel to serial numbers representing dates.

Let’s consider the following dataset imported into Excel from a web page.

It contains date data in column A in the form of text strings having date formats supported by Excel.

Note that the data is left-aligned, meaning Excel recognizes them as text, not dates, even though they look like dates.

We want to use the DATEVALUE function to convert the text strings to numbers representing the dates and display the numbers in column B.

We use the steps below:

1. Select cell B2 and type in the below formula:

=DATEVALUE(A2)

2. Press the Enter key on your keyboard or click Enter on the Formula bar.

3. Drag or double-click the fill handle to copy the formula down the column.

The numbers representing dates are shown in column B.

Using the following steps, we apply a date format to the numbers in column B, so they are displayed as dates in a human-readable form.

4. Select the cell range B2:B2 containing the numbers representing dates.

5. On the Home tab, within the Number group, open the Number Format drop-down and choose either Short Date or Long Date. In this case, we choose Long Date. 

We have converted text to date in Excel, displaying the dates in column B.

Use Format Cells Dialog Box For Greater Control Over the Application of Date Formats

Excel’s Format Cells dialog box is a tool that allows us to change the appearance of cells and perform other formatting operations, such as applying date formats.

We can use the Format Cells dialog box if we want greater control over the application of date formats to the numbers returned by the DATEVALUE function.

We can access the Format Cells dialog box by pressing Ctrl + 1.

We can then use the Date category options on the Number tab to apply several built-in date formats or use the Custom category options to apply custom date formats.

Also read: Split Text to Rows in Excel

Method #2: Use the VALUE Function to Convert Text to Date in Excel

The VALUE function converts a text string representing a number to a number.

So, for example, we can use the function to convert text strings having date formats supported by Excel into numbers representing the dates.

Suppose we have the following dataset imported into Excel from a web page. It contains date data in column A in the form of text strings with date formats recognized by Excel.

The data is left-aligned because Excel recognizes them as text, not dates, even though they look like dates.

We want to apply the VALUE function to convert the date text strings in column A to numbers representing the dates and display them in column B.

We proceed as follows:

1. Select cell B2 and type in the formula below:

=VALUE(A2)

2. Press the Enter key on your keyboard or click Enter on the Formula bar.

Double-click or drag the fill handle to copy the formula down the column.

The numeric values representing dates are shown in column B. 

We use the following steps to apply a date format to the numbers in column B.

4. Select the cell range B2:B6.

5. On the Home tab, in the Number group, open the Number Format drop-down list and choose either Short Date or Long Date. In this case, we choose Short Date. 

We have converted the text in column A to regular dates in column B.

If we want complete control over the application of date formats, we can use the Format Cells dialog box, as explained in Method #1.

Method #3: Use Arithmetic Operators to Convert Text to Date in Excel

There are five arithmetic operators in Excel that we can use to perform basic math operations: Addition (+), multiplication (*), subtraction (-), division (/), and exponentiation (^) operators.

For example, we can apply these operators to convert the text strings with date formats accepted by Excel to numbers representing the dates.

Assuming the text string with an acceptable date format is in cell A2, we can use any of the following formulas to convert the text string to a number representing the date while ensuring that the date is not changed in the calculation:

• =A2+0 Adding a zero to any number doesn’t change the value of the number.
• =A2*1 Multiplying any number by one does not change the value of the number.
• =–A2 Preceding a number with double subtraction is equivalent to multiplying the number by a negative 1 (-1) followed by another negative 1 (-1). The first subtraction converts the text string to a negative number, and the second subtraction changes the negative number to a positive number representing the date. 
• =A2/1 Dividing a number by the value one does not change the value of the number.
• =A^1 Raising a number to the power of 1 does not change the value of the number.

Let’s apply the formulas in the following dataset to convert the text strings in column A to regular dates in columns B, C, D, E, and F.

We use the following steps:

1. Select cell B2 and type in the formula below:

=A2+0

2. Click Enter on the Formula bar and drag or double-click the fill handle to copy the formula down the column.

3. Repeat steps 1-3 in columns C, D, E, and F, remembering to use the right arithmetic operator in the header row.

The formulas have returned the same results. Consequently, we can use any of the five arithmetic operators as a matter of preference.

Let’s apply a date format to the numbers in column B by using the following steps:

4. Select the cell range B2:B6.

5. Press Ctrl + 1, and this will open the Format Cells dialog box.
6. Click the Number tab, select Date on the Category list box, scroll down the Type list box on the right, choose 14-Mar-2012, and click OK.

The date format we selected is now applied to the numbers in column B.

Note: We can use the Custom category options on the Format Cells dialog box if we want to apply custom date formats.

Also read: How to Add Days to Date in Excel

Method #4: Use the Paste Special Dialog Box to Convert Text to Date in Excel

The Paste Special dialog box allows us to paste data in various ways, such as values, formulas, formats, etc. We can use the dialog box to convert text to date in Excel.

The following example dataset has text strings with date formats accepted by Excel.

We use the dataset to show how to use the Paste Special feature to convert text to regular dates.

We use the steps below:

1. Type the number 1 in any empty cell on the worksheet. In this case, we enter the value 1 in cell B1.
2. Select cell B1 containing the number 1 and press Ctrl + C to copy it.

Notice the “marching ants”  border in cell B1 indicating that the value 1 is copied to the Excel clipboard and is available for pasting.

3. Select the cell range A2:A6 containing the text strings.

4. Press Ctrl + Alt + V to activate the Paste Special dialog box (or right-click and then click on Paste Special).

We can also activate the Paste Special dialog box by clicking the Home tab,  opening the Paste drop-down on the Clipboard group, and selecting the Paste Special option as shown below:

5. In the Operation section of the dialog box, select the Multiply option and click OK.

The text strings in column A are converted immediately to numbers representing dates.

Note: What we did using the Paste Special feature is equivalent to applying a formula to multiply the text strings with the number 1, as explained in Method #3. We can also use the Add option on the dialog box.

We apply a date format to the numbers using the following steps:

6. Select the cell range A2:A6.
7. On the Home tab, on the Number group, open the Number Format drop-down and select either Short Date or Long Date. In this case, we choose Long Date.

The Long Date format has been applied to the numbers.

If we want finer control over the application of date formats, we can use the Format Cells dialog box, as explained in Method #1.

Convert Text to Dates in Excel (for text date format not supported by Excel)

This tutorial section deals with converting text strings with date formats not accepted by Excel to regular dates in Excel.

Sometimes when we import data having dates into Excel from the internet or other external databases, the date information may have date formats not supported by Excel.

For example, the following dataset has date information where dots have been used to separate the day, month, and year.

The default date separators that Excel accepts are the hyphen (-) and the forward slash (/).

Consequently, Excel does not recognize this date data as dates but as text strings because they have the dot delimiter. 

If we use the DATEVALUE or VALUE function as we did in Method #1 and Method #2 in the previous section to convert the text strings to numbers representing dates, we get the #VALUE! error.

The DATEVALUE or the VALUE function returns the #VALUE! error because it does not recognize the date text strings with dot delimiters as valid arguments.

To change the format of the text values in columns A to date formats accepted by Excel, we must replace the dot separator with either a hyphen(-) or a forward slash(/). 

We can use the following three techniques to replace the dot separator with a separator that Excel accepts.

Method #5: Using Find and Replace to Change Separator to Forward Slash or Hyphen

The Find and Replace feature in Excel allows us to search for specific text or values in a dataset and replace them with new values.

Therefore we can apply this feature to search for dots in our dataset and replace them with separators that Excel accepts.

In this case, we will replace the dots in our example dataset below with a hyphen (-):

Note: If your dataset has another separator other than a dot, replace the dot separator used in this example with the delimiter in your dataset. 

We use the following steps:

1. Select the cell range A2:A6 containing the text values.

2. On the Home tab, within the Editing group, click the Find & Select button and choose the Replace option on the drop-down.

We can also press the shortcut Ctrl + H to open the Find and Replace dialog box.

3. Type a dot (.) in the Find what box and a hyphen (-) in the Replace with box and click the Replace All button.

The dots in the dataset are replaced immediately with hyphens.

Notice that the values are now right-aligned, meaning that Excel recognizes them as regular dates.

4. Click OK on the informational box that pops up indicating the number of replacements done.

5. Click Close on the Find and Replace dialog box.

Also read: How to Remove Dashes (-) in Excel?

Method #6: Use the Text to Columns Feature to Convert Text to Date in Excel

Excel’s Text to Columns feature enables us to split text in a single cell into separate cells.

In addition, this feature can consolidate the split data into a date.

Suppose we have the following dataset of dates in text format with dots separating the month, day, and year.

We want to use the Text to Columns feature to convert the text strings to regular dates in Excel.

We use the steps below:

1. Select the cell range A2:A6 containing the text values.

2. On the Data tab, in the Data Tools group, click the Text to Columns button.

3. Select Delimited on the Convert Text to Columns Wizard – Step 1 of 3 dialog box and click Next.

4. Ensure no option is selected in the Delimiters options group on the Convert Text to Columns Wizard – Step 2 of 3 dialog box and click Next.

5. On the Convert Text to Columns Wizard – Step 3 of 3 dialog box, select Date, enter =$B$2 in the Destination box, and click Finish.

The text strings in column A have been converted to regular dates in column B.

Notice that some dates are separated with hyphens and others with forward slashes. We can apply a uniform date format to the dates using the Format Cells dialog box as explained in the following steps:

6. Select the cell range B2:B6 containing the regular dates.

7. Press Ctrl + 1 to open the Format Cells dialog box.
8. On the Number tab, select Date on the Category list box, choose the format you want on the Type list box on the right and click OK. In this case, we have chosen March 14, 2012.

The uniform date format is applied suddenly to column B’s regular dates.

Method #7: Use the SUBSTITUTE and DATEVALUE Functions to Convert Text to Numbers Representing Dates

The SUBSTITUTE function replaces existing text with a new text value in a text string, and the DATEVALUE function changes a date in the form of text to a number that represents a date.

We can use a formula combining these two functions to convert dates in text format to numbers representing dates.

We can then apply a date format to the numbers to display dates in a form humans can understand. 

Let’s consider the following dataset having dates in text format with the dot separator separating months, days, and years. 

We want to use a formula combining the SUBSTITUTE and DATEVALUE functions to convert the text strings in column A to numbers representing dates in column B.

We use the following steps:

1. Select cell B2 and type in the following formula:

=DATEVALUE(SUBSTITUTE(A2, ".","-"))

Note: We can use the VALUE function instead of the DATEVALUE function and get the same result.

2. Click Enter on the Formula bar.
3. Double-click or drag the fill handle to copy the formula down the column.

The numbers representing dates appear in column B.

4. Select the cell range B2:B6 containing the numbers representing dates.

5. On the Home tab, in the Number group, open the Number Format drop-down and select either Short Date or Long Date. In this case, we choose Long Date. 

The text strings in column A are now converted to regular dates in column B.

Method #8: Converting Date with Year in XX to 20XX (Two-digit Year to Four-Digit year)

Sometimes we get a dataset with date information in text form with a two-digit year instead of the four-digit year.

The following example dataset has dates in text format with two-digit years.

We can use the Convert XX to 20XX option on the Smart Tag drop-down to convert the text strings to valid dates in Excel.

We use the steps below:

1. Select the cell range A2:A6 containing the text values.

Notice the yellow Smart Tag next to cell A2. We can hover the cursor over the Smart Tag to find out why it appears in the dataset.

In this case, the Smart Tag appears in the dataset because the dataset contains date strings represented with only two digits for the year.

2. Click the down arrow on the Smart Tag to open the drop-down and choose the Convert XX to 20XX option.

Note: We can use the Convert XX to 19XX option if the years were in the 20^th century. 

The text strings are instantly converted to regular dates.

Note: We should only use this method if all the dates are in form 19XX or all the dates are in form 20XX. 

Method #9: Using Text Functions to Convert Text to Date in Excel

Excel has several text functions that allow us to manipulate and extract information from text strings. We can use formulas combining text functions to convert text to date in Excel.

We give examples of how to apply formulas combining text functions to convert text to date in Excel.

Example #1: Converting an 8-Digit Date in Text Format With DDMMYYYY Date Format to Regular Date 

Sometimes we may get a dataset with 8-digit text dates without any delimiter separating the month, day, or year as in the example below.

Notice the regular pattern in the text values: the first two digits represent the day, the next two digits represent the month, and the last four digits represent the year.

We want to use a formula combining the DATE, RIGHT, MID, and LEFT functions to convert the text strings in column A to regular dates in column B.

We proceed as follows:

1. Select cell B2 and type in the below formula:

=DATE(RIGHT(A2,4),MID(A2,3,2),LEFT(A2,2))

2. Click Enter on the Formula bar.
3. Double-click or drag the fill handle to copy the formula down the column.

The formula returns the valid dates in column B.

The RIGHT function extracted the last four digits representing the year, the MID function extracted the month’s two numbers following the day’s first two digits, and the LEFT function pulled the first two digits representing the day.

Finally, the DATE function used the values returned by the text functions as arguments to create a proper date.

Example #2: Convert Text Dates Having Suffixes to Regular Dates

At times we may need to work on datasets that have text dates with suffixes attached to the day values, as in the example dataset below:

We can use a formula combining the DATEVALUE and SUBSTITUTE functions to remove the suffixes and convert the text dates to numbers representing valid dates.

We use the following steps:

1. Select cell B2 and type in the below formula:

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"th",""),"st",""),"rd",""),"nd",""))

2. Click Enter on the Formula bar.
3. Double-click or drag the fill handle to copy the formula down the column.

The text strings in column A are converted to numbers representing dates in column B.

We now apply a date format to the numbers in column B using the following steps:

4. Select the cell range B2:B6 containing the numbers representing dates.

We use the SUBSTITUTE functions to replace the suffixes with empty strings.

We used four SUBSTITUTE functions because four possible suffixes can be attached to the day values: st, nd, rd, and th. 

After the suffixes are replaced with empty strings, the DATEVALUE function converts the text strings to numbers representing dates.

5. On the Home tab, in the Number group, open the Number Format drop-down and select either Short Date or Long Date. In this case, we choose Long Date. 

The text strings in column A have been converted to valid dates in column B.

We can use the Format Cells dialog box, as explained in Method #1, to apply custom date formats or other built-in date formats not available on the Number Format drop-down. 

Example #3: Convert Text Dates With a Day Name to Regular Dates in Excel.

Sometimes we may receive datasets with text dates having the day names, as in the example below:

We can use a formula combining the DATEVALUE, RIGHT, LEN, and FIND functions to remove the day names from the dataset and convert the remaining text strings to numbers representing dates.

We use the following steps:

1. Select cell B2 and type in the below formula:

=DATEVALUE(RIGHT(A2,LEN(A2)-FIND(" ",A2)))

2. Click Enter on the Formula bar.
3. Drag or double-click the fill to copy the formula down the column.

The FIND function returns the position number of the first space character in the text string.

Next, the position number is subtracted from the length of the text string, and the result is fed into the RIGHT function.

Next, the RIGHT function uses the result as an argument to extract the text date without the day name.

Finally, the text string returned by the RIGHT function is fed into the DATEVALUE function, which returns the serial number representing the date.

4. Select the cell range B2:B6 containing the numbers representing dates.
5. On the Home tab, in the Number group, open the Number Format drop-down and select either Short Date or Long Date. In this case, we choose Long Date. 

The text dates in column A with day names have been converted to regular dates in column B.

This tutorial showed various techniques for converting text to date in Excel. Suppose the text strings have date formats supported by Excel.

In that case, we can use the DATEVALUE and VALUE functions or the Paste Special feature to generate the serial numbers representing the dates.

We can then apply date formats to the serial numbers to display the dates in a form understandable to humans.

We can also use arithmetic formulas to convert the text strings to serial numbers representing dates and then apply date formats to the numbers.

If the text strings have date formats not supported by Excel, we can use formulas combining text functions to manipulate the text strings and convert them to regular dates.

We hope you found the tutorial helpful. 

Other Excel articles you may also like:

• How to Insert Date in Excel? (Shortcut + Formulas)
• How to Convert Date to Serial Number in Excel?
• How to Remove Year from Date in Excel?
• How to Separate Date and Time in Excel?
• How To Combine Date and Time in Excel?
• How to Highlight Dates Before Today in Excel?
• How to Calculate the Number of Months Between Two Dates in Excel?