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

If you are looking for some of the easiest ways to convert text to date in Excel, then you will find this article useful. So, let’s dive into the article.

How to Convert Text to Date in Excel: 10 Ways

I have the following data table where I have Dates in the text format in the Date column as you can see these text dates are Left-aligned. The real dates will be Right-aligned. Here, the text dates are in dd-mm-yyyy format. Here, I will use this data table to explain the methods of converting text to date.
I have used Microsoft Excel 365 version for this purpose, you can use any other version according to your convenience.

Method-1: Change Format Using Number Format to Covert Text to Date

Here, you can see the format of the values in the Date column is in Text format. You can change the format from Text to Date by following this method.

Steps:
➤Firstly, select the Date column.
➤Then, press CTRL+1.

Now, the Format Cells Dialog Box will open.

➤Next, select the Category as Date option.
➤Afterward, choose any type as your wish in the Type Option, here I have chosen the mm/dd/yyyy format.
➤Lastly, press OK.

Result:
After that, you will get the dates in the Date format.

Read More: How to Convert General Format to Date in Excel

Method-2: Use VALUE function to Convert Text to Date

You can use the VALUE function to convert text to date. Here, I have added the Real Date column.

Steps:
➤In the beginning, select Cell E5.
➤Then, type the following formula

It will convert the text into a value.

➤After that, press ENTER.
➤Next, drag down the Fill Handle Tool

Afterward, you will get the corresponding values of the texts in the Real Date column.

➤Lastly, follow Method-1 in the Real Date column to change the format into Date.

Result:
Now, you will get the dates in the Real Date column.

Method-3: Convert Text to Date Using DATEVALUE Function in Excel

If you want to convert Text to Date, then you can use the DATEVALUE function.

Step-01:
➤To start with, select Cell E5.
➤After that, type the following formula

It will convert the text date into a value.

➤Next, press ENTER and drag down the Fill Handle Tool.

Then, 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:
Afterward, you will get the dates in the Real Date column.

Method-4: Use Mathematical Operators to Convert Text to Date

You can use different types of mathematical operators like Addition, Double Negation, Multiplication, and Division Operators to convert Text to Date in the Real Date column.

Steps:
➤Firstly, select Cell E5.
➤Secondly, type the following formula

Now, it will convert the text into a value

➤Then, press ENTER.

➤Now, select Cell E6.
➤After that, type the following formula

It will convert the text into a value

➤Press ENTER.

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

➤Finally, follow Method-1 in the Real Date column to change the format into Date.

Result:
After that, you will get the dates in the Real Date column.

Method-5:Convert Text to Date Using Find & Select Option

In the Date column in the text dates the month, date and year are separated by using the “.” sign and for this separator, you can’t convert them into Date format. For this reason, you have to replace them at first with a “/” separator. You can use the Find & Select Option for this purpose.

Steps:
➤In the beginning, go to Home Tab>>Editing Dropdown>>Find & Select Dropdown>>Find Option

Then, the Find and Replace Wizard will appear.

➤Then, select the Replace Option
➤Afterward, write down “.” in the Find what Box and “/” in the Replace with Box.
➤Lastly, click Replace All

After that, another Wizard will pop up and select OK.

Then, 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:
Thus, you will get the dates in the Real Date column.

Read More: Text Won’t Convert to Date in Excel

Method-6: Change Date Delimiters Using SUBSTITUTE function in Excel

Prior to converting Text to Date, at first, you have to replace “.” with “/” separator. You can use the SUBSTITUTE function for this purpose. So, I have added two columns named Changed Date and Real Date.

Steps:
➤Firstly, select Cell E5
➤Type the following formula

Now, it will replace “.” with “/”

➤Then, press ENTER and Drag down the Fill Handle Tool

After that, 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:
In this way, you will get the dates in the Real Date column.

Method-7:Use Text to Columns Option to Convert Text to Date in Excel

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

Steps:
➤In the beginning, go to Data Tab>>Data Tools Group>>Text to Columns Option

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

➤After that, select the Delimited Option
➤Then, click Next.

In stage-2 you don’t have to select any type of Delimiters, just click Next.

➤Next, select the Date format MDY (you can choose any other type according to your data)
➤Then, type the cell name where you want the output in the Destination Box
➤Lastly, click Finish

Result:
Afterward, you will get the Dates in the Real Date column.

Method-8: Convert Complex Text to Date Using Text to Columns Option in Excel

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

Steps:
➤To start with, go to Data Tab>>Data Tools Group>>Text to Columns Option

Now, Convert Text to Columns Wizard will appear which has 3 stages.

➤Select the Delimited Option
➤Then, click Next

➤After that, select Comma, Space as Delimiters
➤Click the Treat consecutive delimiters as one Option
➤Then,, press Next

➤Next, select Do not import column(skip) Option to skip the name of the days
➤Type the cell name where you want the output in the Destination Box
➤Lastly, click Finish

After that, you will get the split data in three columns containing Month, Day, Year.

➤Now, select Cell D6
➤Type the following formula

➤After that, press ENTER and drag down the Fill Handle Tool.

Result:
Then, you will get the Dates in the Real Date column.

Method-9: Use Excel Paste Special Feature to Convert Text to Date

You can convert Text to Date easily by using the Paste Special Option. To do this you need value 0 which is here in Cell E5.

Steps:
➤Firstly, select Cell E5 and press CTRL+C

➤After that, select the range of Texts in the Date column.
➤Next, right-click on your mouse.
➤Then, select Paste Special Option.

Then, the Paste Special Dialog Box will appear.

➤Select All as a Paste Option and Add as Operation Option
➤Finally, press OK.

After that, the texts will be converted into values

➤Follow Method-1 to change the format to Date.

Result:
Now, you will get the dates in the Date column.

Method-10: Change 8 Digit Numbers to Date in Excel

If you have Dates as Numbers like below you can convert them into Dates by using the DATE, LEFT, RIGHT, and MID functions.

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.

Firstly, we will change the Numbers in the green box which are in the yyyy-mm-dd format to Dates.

Step-01:
➤To start with, select Cell E5.
➤Type the following formula

➤After that, press ENTER and copy the formula for Cell E6
Then, you will get the Dates.

Secondly, we will change the Numbers in the red box which are in the dd-mm-yyyy format to Dates.

Step-02:
➤Select Cell E7
➤Type the following formula

➤Press ENTER and copy the formula for Cell E8
After that, you will get the Dates.

Lastly, we will change the Numbers in the blue box which are in the yy-dd-mm format to Dates.

Step-03:
➤Select Cell E9
➤Type the following formula

➤Press ENTER and copy the formula for Cell E10
Afterward, you will get the Dates.

Read More: How to Convert Number to Date in Excel

Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

Download Workbook

Conclusion

In this article, I tried to cover the easiest ways to convert text to date effectively. Hope you will find it useful. If you have any suggestions or questions feel free to share them with us.

Related Articles

• How to Convert 8 Digit Number to Date in Excel
• How to Convert Number (YYYYMMDD) to Date Format in Excel
• How to Convert Serial Number to Date in Excel
• How to Convert Text to Date and Time in Excel
• How to Convert Text Date and Time to Date Format in Excel
• How to Disable Auto Convert to Date in Excel

<< Go Back to Convert to Date | Date-Time in Excel | Learn Excel