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

## Dataset Overview

Let’s consider a dataset containing employee information, including their joining dates. Unfortunately, these dates are not in the correct date format.

### Solution 1 – Applying Find and Replace

In our dataset, we have separated date components with commas (,). The values are in General format

When trying to change the format of the values to date, it does not change.

Herewith the solution:

• Select the range C4:C8.
• Go to the Home tab, click on Editing, and choose Find & Select, then Replace.

• In the Find what: box, enter a comma (,) and in the Replace with: box, enter a backslash (/).
• Click Replace All.

• Confirm the replacement procedure in the Message Box and click OK.

• Excel will convert the text to the desired date format.

Read More: How to Convert Number to Date in Excel

### Solution 2 – Using the SUBSTITUTE Function

Here, we have separated the date components with a full stop (.).

When we trying to change the format to Date, it actually does not occur in Excel.

Herewith the solution:

• In cell D4, enter the formula:
`=SUBSTITUTE(C4,".","/")`

This formula will replace all the full stops (.) with backslashes (/).

• Then press ENTER. Excel will convert the text to Date format.

• Use Fill Handle to AutoFill the formula down to cell D8

### Solution 3 – Applying Combined Functions

Here, if we view the data in the Formula Bar, the data contains an apostrophe ().

When trying to change the format of the data to Date, it does not change.

Herewith the solution:

To handle both date and time components, we’ll use the DATEVALUE and TIMEVALUE functions.

• In cell D4, enter 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.

• Press ENTER.

• Use the Fill Handle to AutoFill the formula down to cell D8.

Formatting the Result

• Select the range D4:D8.
• Go to the Home tab, choose Number, and then select More Number Formats.

• In the Format Cells window, select Custom.

• Enter 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.

• Click OK.

Excel will now display the date and time in your desired format.

Read More: How to Convert Text to Date in Excel

### Solution 4 – Using Text to Column Wizard to Convert Text to Date in Excel

In this section, we’ll explore how to convert text to dates using the Text to Column Wizard. Let’s consider the value in cell C4, which is currently in text format. Our goal is to convert it to the proper date format.

When trying to change the format to Date, it does not work.

Herewith the solution:

Select the Range:

• Highlight the range C4:C8.

Access the Text to Columns Tool:

• Go to the Data tab.
• Click on Data Tools.
• Choose Text to Columns.

Configure the Wizard:

• In the new window, select Delimited and click Next.

• Unmark all the boxes (since we don’t have any delimiters).
• Click Next.

Specify Date Format:

• Select Date.
• Choose the appropriate date format (e.g., MDY for Month/Date/Year).
• Select the destination cell (e.g., D4).
• Click Finish.

Excel will now convert the text to the desired Date format in the specified destination cell.

## Practice Workbook

A workbook has been attached so that you can practice these solutions.

You can download the practice workbook from here:

## Related Articles

<< Go Back to Convert to Date | Date-Time in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Akib Bin Rashid

AKIB BIN RASHID, a materials and metallurgical engineer, is passionate about delving into Excel and VBA programming. To him, programming is a valuable time-saving tool for managing data, files, and internet-related tasks. Proficient in MS Office, AutoCAD, Excel, and VBA, he goes beyond the fundamentals. Holding a B.Sc in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, MD AKIB has transitioned into a content development role. Specializing in creating technical content centred around Excel and... Read Full Bio

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

Advanced Excel Exercises with Solutions PDF