How to Convert Text to Date in Excel (10 Ways)

Here’s an overview of a list of dates in textual values that will be converted to dates.

How to Convert Text to Date in Excel: 10 Ways

We have Dates in the text format in the Date column which are Left-aligned. The real dates will be Right-aligned. The text dates are in dd-mm-yyyy format. We will use this data table to explain the methods of converting text to date.

Method 1 – Change the Format Using the Number Format

We’ll use the sample dataset.

Steps:

Select the Date column.
Press Ctrl + 1.

The Format Cells Dialog Box will open.

Select the Category as Date option.
Choose any type as your wish in the Type option. We have chosen the mm/dd/yyyy format.
Press OK.

Result:

Method 2 – Use the VALUE function

We have added the Real Date column.

Steps:

Select Cell E5.
Insert the following formula:

=VALUE(C5)

The VALUE function converts the text in Cell C5 into a numeric value.

Press Enter.
Drag down the Fill Handle tool.

You will get the corresponding values of the texts in the Real Date column.

Follow Method 1 in the Real Date column to change the format into Date.

Result:

Method 3 – Using the DATEVALUE Function

We’ll use the sample dataset.

Steps:

Select Cell E5.
Insert the following formula:

=DATEVALUE(C5)

In the formula, the DATEVALUE function converts the text in Cell C5 into a numeric value.

Press Enter and drag down the Fill Handle tool.

You will get the corresponding values of the texts in the Real Date column

Follow Method 1 in the Real Date column to change the format into Date.

Result:

Method 4 – Use Mathematical Operators

You can use Addition, Double Negation, Multiplication, and Division operators.

Steps:

Select Cell E5.
Insert the following formula:

=C5+0

Press Enter.

Here, in the formula, adding 0 to the text converts it into numeric value.

Select Cell E6.
Use the following formula

=--C6

Press Enter.

In the formula, adding a double negative to the text converts it into numeric value.

You can convert the text into a value by using Multiplication and Division Operator as follows.

Follow Method 1 in the Real Date column to change the format into Date.

Result:

Method 5 – Using the Find & Select Option

In the Date column in the text dates the month, date and year are separated by using the “.” sign. We’ll replace them with a “/” separator.

Steps:

Go to the Home tab.
Select the Editing drop-down.
Choose Find & Select and select Find.

The Find and Replace Wizard will appear.

Select the Replace option.
Write “.” in the Find what box and “/” in the Replace with box.
Click Replace All.

Another Wizard will pop up and select OK.

The text dates will have the replaced separator.

Follow Method 1 in the Real Date column to change the format from Text into Date.

Result:

Method 6 – Change Date Delimiters Using the SUBSTITUTE function

We’ll replace “.” with the “/” separator. We have added two columns named Changed Date and Real Date.

Steps:

Select Cell E5.
Insert the following formula:

=SUBSTITUTE(C5,".","/")

In the formula, the SUBSTITUTE function replaces “.” by “/” inCell C5.

Press ENTER and drag down the Fill Handle tool.

The text dates will have the replaced separator.

Follow Method 1 in the Real Date column to change the format from Text into Date.

Result:

Method 7 – Use Text to Columns

You can use the Text to Columns Option to convert Text dates with any type of separator to Date format.

Steps:

Go to the Data tab and, in the Data Tools group, select the Text to Columns option.

The Convert Text to Columns Wizard will open, which has 3 stages.

Select the Delimited Option
Click Next.

In Step 2, click Next.

Select the Date format MDY (you can choose any other type according to your data).
Type the cell where you want the output in the Destination box.
Click Finish.

Result:

Method 8 – Convert a Complex Text to a Date Using Text to Columns Option

If you have complex dates like below to convert into Date, then you can use the Text to Columns Option and the DATE function.

Steps:

Go to Text to Columns.

The Convert Text to Columns Wizard will appear which has 3 Steps.

Select the Delimited option.
Click Next.

Select Comma and Space as Delimiters.
Click the Treat consecutive delimiters as one option.
Press Next.

Select Do not import column(skip) to skip the name of the days.
Type the cell name where you want the output in the Destination box.
Click Finish.

You will get the split data in three columns containing Month, Day, and Year.

Select Cell D6.
Insert the following formula:

=DATE(G5,MONTH(1&E5),F5)

G5 will give the Year value
MONTH(1&E5) will convert the Month into the corresponding Month number.
F5 is Day

Press Enter and drag down the Fill Handle tool.

Result:

Method 9 – Use Excel Paste Special

You need a value 0, which is in Cell E5.

Steps:

Select Cell E5 and press Ctrl + C.

Select the range of Texts in the Date column.
Right-click on the selection.
Select Paste Special.

The Paste Special Dialog Box will appear.

Select All for Paste and Add as Operation.
Press OK.

The texts will be converted into values.

Follow Method 1 to change the format to Date.

Result:

Method 10 – Change 8-Digit or 6-Digit Numbers to Dates

Here, the numbers in the green box are in the yyyy-mm-dd format, the numbers in the red box are in the dd-mm-yyyy format, and the numbers in the blue box are in the yy-dd-mm format.