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.

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 mm-dd-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.

convert text to date

Method-1: Changing Format from 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.

convert text to date

Step-01:
➤Select the Date column
➤Press CTRL+1

changing format

Then, the Format Cells Dialog Box will open.

changing format

➤Select the Category as Date
➤Choose any type as your wish in the Type Option, here I have chosen the mm/dd/yyyy format.
➤Press OK

changing format

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

convert text to date

Read More: How to Convert General Format to Date in Excel (7 Methods)

Method-2: Using 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.

convert text to date

Step-01:
➤Select Cell E5
➤Type the following formula

=VALUE(C5)

It will convert the text into a value.

VALUE function

➤Press ENTER
➤Drag down the Fill Handle Tool

VALUE function

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

convert text to date

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

changing format

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

convert text to date

Method-3: Using DATEVALUE function

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

convert text to date

Step-01:
➤Select Cell E5
➤Type the following formula

=DATEVALUE(C5)

It will convert the text date into a value.

DATEVALUE function

➤Press ENTER
➤Drag down the Fill Handle Tool

DATEVALUE function

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

convert text to date

➤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.

convert text to date

Method-4: Using 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.

convert text to date

Step-01:
➤Select Cell E5
➤Type the following formula

=C5+0

It will convert the text into a value

mathematical operator

➤Press ENTER

mathematical operator

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

=--C6

It will convert the text into a value

mathematical operator

➤Press ENTER

convert text to date

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

convert text to date

mathematical operator

➤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.

convert text to date

Method-5: Changing Date Delimiters 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.

convert text to date

Step-01:
➤Go to Home Tab>>Editing Dropdown>>Find & Select Dropdown>>Find Option

Find & Select Option

Then, the Find and Replace Wizard will appear.

➤Select the Replace Option
➤Write down “.” in the Find what Box and “/” in the Replace with Box.
➤Click Replace All

Find & Select Option

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

Find & Select Option

Then the text dates will have the replaced separator.

convert text to date

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

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

convert text to date


Similar Readings


Method-6: Changing Date Delimiters using the SUBSTITUTE function

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.

convert text to date

Step-01:
➤Select Cell E5
➤Type the following formula

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

It will replace “.” with “/”

SUBSTITUTE function

➤Press ENTER
➤Drag down the Fill Handle Tool

SUBSTITUTE function

After that, the text dates will have the replaced separator.

SUBSTITUTE function

➤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.

convert text to date

Method-7:Using Text to Columns Option

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

convert text to date

Step-01:
➤Go to Data Tab>>Data Tools Group>>Text to Columns Option

Text to Columns Option

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

➤Select the Delimited Option
➤Click Next

Text to Columns Option

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

Text to Columns Option

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

Text to Columns Option

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

convert text to date

Method-8: Converting Complex Text to Date

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.

convert text to date

Step-01:
➤Go to Data Tab>>Data Tools Group>>Text to Columns Option

Text to Columns Option

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

➤Select the Delimited Option
➤Click Next

Text to Columns Option

➤Select Comma, Space as Delimiters
➤Click the Treat consecutive delimiters as one Option
➤Press Next

Text to Columns Option

➤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
➤Click Finish

Text to Columns Option

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

Text to Columns Option

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

=DATE(G6,MONTH(1&E6),F6)

G6 will give the Year value
MONTH(1&E6) will convert the Month into the corresponding Month number.
F6 is Day

Text to Columns Option

➤Press ENTER
➤Drag down the Fill Handle Tool.

convert text to date

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

convert text to date

Method-9: Using Paste Special

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.

convert text to date

Step-01:
➤Select Cell E5 and press CTRL+C

Paste Special

Step-02:
➤Select the range of Texts in the Date column
➤Right-click on your mouse
➤Select Paste Special Option

Paste Special

Then, the Paste Special Dialog Box will appear.

➤Select All as a Paste Option and Add as Operation Option
➤Press OK

Paste Special

After that, the texts will be converted into values

convert text to date

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

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

convert text to date

Method-10: Changing 8 digit Numbers to Date

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.

convert text to date

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

Step-01:
➤Select Cell E5
➤Type the following formula

=DATE(LEFT(C5,4),MID(C5,5,2),RIGHT(C5,2))

LEFT(C5,4) will give the Year value
MID(C5,5,2) is Month.
RIGHT(C5,2) provides the Day value

DATE function

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

DATE function

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))

RIGHT(C7,4) will give the Year value
MID(C7,3,2) is Month.
LEFT(C7,2) provides theDay value

DATE function

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

DATE function

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))

20&LEFT(C9,2) will give the Year value
RIGHT(C9,2) is Month.
MID(C9,3,2) provides the Day value

DATE function

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

convert text to date

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.

Practice

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

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo