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

Get FREE Advanced Excel Exercises with 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.

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.

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`
âž¤ Then click OK.

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

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.

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.

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Akib Bin Rashid

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

Advanced Excel Exercises with Solutions PDF