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.
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 Solutions
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.Â
Now when we try to transform this format to Date, it actually does not occur 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.
Find and Replace dialog box will appear.
➤ Type comma (,) in the Find what: box and backslash (/) in the Replace with: box. Then click Replace All.
A Message Box will appear confirming the replacement procedure. Click OK.
Excel will convert the text to Date Format.
Read More: How to Convert Number to Date in Excel
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 (.).
Now when we try to transform this format to Date, it actually does not occur 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 converts all the text to Date.
Read More: How to Convert General Format to Date in Excel
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 and TIMEVALUE functions. Notice that there is an apostrophe (‘) in the Formula bar.
Now if we convert this dataset into Date format, it doesn’t change.
We will solve this issue now.
STEPS:
➤ Select cell D4 and write down the formula.
=DATEVALUE(C4)+TIMEVALUE(C4)
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.
➤ Now use Fill Handle to AutoFill up to D8.
➤ 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.
➤ A Format Cells window will appear.
➤ Select Custom and write down the following format.
dd/mm/yyyy hh:mm AM/PM
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.
Read More: How to Convert Text to Date in Excel
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.
 But it does not convert to date when we choose the Date format.
I will show you how to solve this problem.
STEP:
➤ First, select the range C4:C8. Then go to Data tab >> select Data Tools >> select Text to Columns
➤ A new window will appear. Select Delimited >> select Next.
➤ Then unmark all the boxes and click Next.Â
➤ Then select the Date >> choose the format >> select Destination >> click Finish.
We have chosen the format as MDY (Month/Date/Year), and Destination as D4.
Then Excel will convert the text to Date format in the selected destination.Â
Read More: How to Convert Text to Date and Time in Excel
Practice Workbook
It is a bit complex to apply a solution if text won’t convert to date in Excel. So, you should practice getting the hang of this task. That’s why I have attached a practice sheet for you.
Download Practice Workbook
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
- How to Convert Serial Number to Date in Excel
- How to Convert 8 Digit Number to Date in Excel
- How to Convert Number (YYYYMMDD) to Date Format 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
Get FREE Advanced Excel Exercises with Solutions!
Many users have this same problem but it’s hard to find an answer when you use Convert text to date but not all the cells got converted to a date. What to do?
Dear DANIELLA,
Thanks for your comment. Have you tried the methods mentioned in this article to solve the issue? If you need further assistance, you can share your file in our Exceldemy Forum (https://exceldemy.com/forum/).
Regards
Aniruddah
Team Exceldemy