Text Won’t Convert to Date in Excel (4 Problems & Solutions)

We often need to work with different types of huge datasets in Excel. The data can be of various types such as Number, Text, Date, etc. So often people face problems when a Date is in Text format and the Text won’t convert to Date in Excel. In this article, I will discuss 4 methods along with different cases and show how to convert Text to Date format.


Download Practice Workbook

This is the dataset for this article. We have some Employee along with their Joining Date. But the dates are not in proper Date format. So people may face the problem while converting these to Date. With this dataset, I will show you how to solve this problem.

text won't convert to date in excel


4 Methods to Solve If Text Won’t Convert to Date in Excel

1. Applying Find and Replace If Text Won’t Convert to Date in Excel

We can use the Find and Replace dialogue box if the text won’t convert to date in Excel. In our dataset, we have separated date components by commas. Notice that we have the values in General format

text won't convert to date in excel
Now when we try to transform this format to Date, it actually does not occur in Excel.

text won't convert to date in excel
Now, let me describe to you the procedure of how you can convert these stubborn text formats to Date format in Excel.

STEPS:
➤ Select the range C4:C8.
➤ Go to Home tab >> select Editing >> select Find & Select >> select Replace.

text won't convert to date excel

Find and Replace dialog box will appear.

text won't convert to date in excel

➤ Type comma (,) in the Find what: box and backslash (/) in the Replace with: box. Then click Replace All.

text won't convert to date excel

A Message Box will appear confirming the replacement procedure. Click OK.

Excel will convert the text to Date Format.

text won't convert to date in excel

Read More: How to Convert Text to Date in Excel (10 ways)


2. Using SUBSTITUTE Function to Convert Text to Date

Now we are going to apply the SUBSTITUTE function if our text won’t convert to date in Excel. Here, we have separated the date components with a full stop (.).

text won't convert to date in excel

Now when we try to transform this format to Date, it actually does not occur in Excel.

text won't convert to date in excel

We will show how to get rid of this problem in Excel.

STEP:
➤ Go to cell D4 and write down the formula

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

This formula will substitute all the full stops (.) with a backslash (/).
➤ Then press ENTER. Excel will convert the text to Date format.

 Then use Fill Handle to AutoFill the up to D8. Excel will convert all the text to Date.

Related Content: How to Convert General Format to Date in Excel (7 Methods)


Similar Readings:


3. Applying Combined Functions to Convert Text to Date

We often need to convert both the date and time to the proper format. We can do so by using the DATEVALUE function and TIMEVALUE function. Notice that there is an apostrophe () in the Formula bar.


Now if we convert this dataset into Date format, it doesn’t change.

text won't convert to date in excel

We will solve this issue now.

STEPS:
➤ Select cell D4 and write down the formula.

=DATEVALUE(C4)+TIMEVALUE(C4)

text won't convert to date in excel

Formula Breakdown

DATEVALUE(C4) >> Converts the date into a numerical value.
          Output is >> 40295
          Explanation >> Date value of 40295 is 4/27/2010

TIMEVALUE(C4) >> Converts the time into a numerical value.
          Output is >> 0.375
          Explanation >> Time value of 0.375 is 09:00 AM

DATEVALUE(C4)+TIMEVALUE(C4) >> Adds up the numerical values of date and time.
          Output is >> 40295.375
          Explanation >> Here 40295 denotes Date  and 0.375 denotes Time.

➤ Then press ENTER. Excel will get you the resultant value in Numeric format.

text won't convert to date in excel
➤ Now use Fill Handle to AutoFill up to D8.

text won't convert to date in excel

➤ Now we need to format this value in our desired format. To do so,
➤ Select the range D4:D8
➤ Go to Home tab >> select Number >> select More Number Formats.

text won't convert to date in excel

A Format Cells window will appear.

Text won't convert to date in Excel

➤ Select Custom and write down the following format.

dd/mm/yyyy hh:mm AM/PM
➤ Then click OK

Text won't convert to date in Excel

Format Breakdown

dd/mm/yyyy hh:mm AM/PM >> We used this custom format to convert the text date and time to date format.
      dd —>It represents the date.
      mm —> It represents the month.
      yyyy —>  This represents the year.
      hh —> It denotes the hour of the day.
      mm —> It stands for a minute.
Then, depending on the time, AM or PM will appear.

Excel will format the date and time to your desired format.

Text won't convert to Date in Excel

Read More: Convert Text to Date and Time in Excel (5 Methods)


4. Using Text to Column Wizard to Convert Text to Date in Excel

In this section, we will discuss how to convert text to date using Text to Column Wizard. Notice that the value in C4 is in Text format. We need to convert this to Date format.

text won't convert to date in excel

 But it does not convert to date when we choose Date format.

Text won't convert to date in Excel
I will show how to solve this problem.

STEP:
➤ First, select the range C4:C8. Then go to Data tab >> select Data Tools >> select Text to Columns

text won't convert to date in excel

➤ A new window will appear. Select Delimited >> select Next.

text won't convert to date excel
➤ Then unmark all the boxes and click Next

text won't convert to date in excel

➤ Then select the Date >> choose the format >> select Destination >> click Finish.
We have chosen the format as MDY (Month/Date/Year), Destination as D4.

text won't convert to date excel

Then Excel will convert the text to Date format in the selected destination. 

Read More: How to Convert Number to Date in Excel (6 Easy Ways)


Practice Workbook

It is a bit complex to apply a solution if text won’t convert to date in Excel. So you should practice to get the hang of this task. That’s why I have attached a practice sheet for you.

text won't convert to date excel


Conclusion

In this article, I have given 4 solutions if text won’t convert to date in Excel. I hope you will find this helpful. Lastly, if you have any queries or suggestions, please leave them in the comment box.


Related Articles

Akib

Akib

Hi, this is MD Akib Bin Rashid. I completed my BSc in Engineering back in 2019. After that, I did an MBA. Then, I joined as an Excel and VBA Content Developer at SOFTEKO Digital. Being passionate about data analytics and finance, I will try to assist you with Excel

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo