Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.

Convert Text to Date Preview


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.

Dataset to Convert Text to Date in Excel


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.

Change Format Using Number Format to Convert Text to Date in Excel

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

selecting data

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

changing format

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

Convert Text to Date Result Using Number 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.

data in text format

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

=VALUE(C5)

It will convert the text into a value.

Using VALUE function to Convert Text to Date in Excel

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

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

using fill handle

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

output of VALUE function

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

changing format to get data in date format

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

Convert Text to Date Result Using VALUE Function


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.

Dataset to convert text to date using 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.

applying DATEVALUE function

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

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

using fill handle to copy formula

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

output of DATEVALUE function

➤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 result Using DATEVALUE Function in Excel


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.

date in text format to change text to date

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

=C5+0

Now, it will convert the text into a value

➤Then, press ENTER

adding 0 with text to convert text to date in excel

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

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

=--C6

It will convert the text into a value

➤Press ENTER

Using double negative before text to convert text to date in excel

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

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

multiplying text by 1 to convert text to date in excel

dividing text by 1 to convert text to date in excel

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

Convert Text to Date result by math operator


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.

date in text format to use Find & Select Feature

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

selecting Find and Replace to convert text to date in excel

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

using find and replace box to convert text to date in excel

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

change after replace

Then, the text dates will have the replaced separator.

format after replace

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

Convert Text to Date result using find and replace feature


Similar Readings


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.

date in text format to use SUBSTITUTE function

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

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

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

using SUBSTITUTE function to convert text to date in excel

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

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

applying fill handle tool to copy formula with SUBSTITUTE function

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

result of 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 result using SUBSTITUTE function in Excel


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.

date in text format to use Text to Columns option

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

using text to columns option to convert text to date in excel

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

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

Opening convert text to columns wizard

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

Text to Columns Option

➤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

final step of using Text to Columns Option

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

Convert Text to Date result using text to columns option in excel


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.

complex text to date dataset

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

applying data tools

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

➤Select the Delimited Option
➤Then, click Next

Text to Columns Option for complex data

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

second step to use Text to Columns Option

➤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

final step to use Text to Columns Option for complex dataset

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

splitted data from date in text format➤Now, select Cell D6
➤Type the following formula

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

using date function to convert text to date in excel for complex data

In the formula,

G5 will give the Year value
MONTH(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.

using fill handle to copy formula for complex data

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

Convert Text to Date output


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.

date in text format to use paste special method

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

copying 0 to convert text to date in excel➤After that, select the range of Texts in the Date column
➤Next, right-click on your mouse.
➤Then, select Paste Special Option.

opening paste special

Then, the Paste Special Dialog Box will appear.

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

opening Paste Special box

After that, the texts will be converted into values

output of paste special feature

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

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

Convert Text to Date result using paste special in Excel


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.

8 digit numbers

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.

using date left mid right functions to convert text to date in excel

In the formula,

LEFT(C5,4) will give the Year value
MID(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.

using date left mid right functions

In the formula,

RIGHT(C7,4) will give the Year value
MID(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.

using date left mid right functions for another format

In the formula,

20&LEFT(C9,2) will give the Year value
RIGHT(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.

practice section


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