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.
Download Workbook
10 Ways to Convert Text to Date in Excel
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,elect the Category as Date
➤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 (7 Methods)
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
=VALUE(C5)
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
=DATEVALUE(C5)
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
=C5+0
Now, it will convert the text into a value
➤Then, press ENTER
➤Now, select Cell E6
➤After that, type the following formula
=--C6
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 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.
Similar Readings
- Text Won’t Convert to Date in Excel (4 Problems & Solutions)
- How to Convert Text Date and Time to Date Format in Excel (7 Easy Ways)
- Convert Unix Timestamp to Date in Excel (3 Methods)
- How to Convert Active Directory Timestamp to Date in Excel (4 Methods)
- How to Disable Auto Convert to Date in Excel (2 Methods)
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
=SUBSTITUTE(C5,".","/")
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
=DATE(G5,MONTH(1&E5),F5)
In the formula,
G5 will give the Year valueMONTH(1&E5)
will convert the Month into the corresponding Month number.
F5 is Day
➤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 into 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 function, the LEFT function, the RIGHT function, the MID function.
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
=DATE(LEFT(C5,4),MID(C5,5,2),RIGHT(C5,2))
➤After that, press ENTER and copy the formula for Cell E6
Then, you will get the Dates.
In the formula,
LEFT(C5,4)
will give the Year valueMID(C5,5,2)
is Month.RIGHT(C5,2)
provides the Day value
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
=DATE(RIGHT(C7,4),MID(C7,3,2),LEFT(C7,2))
➤Press ENTER and copy the formula for Cell E8
After that, you will get the Dates.
In the formula,
RIGHT(C7,4)
will give the Year valueMID(C7,3,2)
is Month.LEFT(C7,2)
provides theDay value
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
=DATE(20&LEFT(C9,2),RIGHT(C9,2),MID(C9,3,2))
➤Press ENTER and copy the formula for Cell E10
Afterward, you will get the Dates.
In the formula,
20&LEFT(C9,2)
will give the Year valueRIGHT(C9,2)
is Month.MID(C9,3,2)
provides the Day value
Read More: How to Convert Number to Date in Excel (6 Easy Ways)
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.
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.