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

When working with large datasets in Excel, we often encounter various data types such as numbers, text, and dates. One common issue arises when dates are stored as text, and we need to convert them to the proper date format. In this article, I’ll discuss four methods to address this problem.

Let’s consider a dataset containing employee information, including their joining dates. Unfortunately, these dates are not in the correct date format. I’ll guide you through the solutions to convert them successfully.

text won't convert to date in excel


Solution 1: Applying Find and Replace 

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

text won't convert to date in excel
When trying to change the format of the values to date, it does not change.

text won't convert to date in excel
Herewith the solution:

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

text won't convert to date excel

text won't convert to date in excel

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

text won't convert to date excel

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

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

text won't convert to date in excel

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 (.).

text won't convert to date in excel

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

text won't convert to date 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 down to cell D8

Read More: How to Convert General Format to Date in Excel


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.

text won't convert to date in excel

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)

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.

  • Press ENTER.

text won't convert to date in excel

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

text won't convert to date in excel

Formatting the Result

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

text won't convert to date in excel

  • In the Format Cells window, select Custom.

Text won't convert to date in Excel

  • Enter the following format:

dd/mm/yyyy hh:mm AM/PM
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.

  • Click OK.

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

Text won't convert to Date in Excel

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. 

text won't convert to date in excel

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

Text won't convert to date in Excel
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.

text won't convert to date in excel

Configure the Wizard:

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

text won't convert to date excel

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

 

text won't convert to date in excel

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.

text won't convert to date excel

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

Read More: How to Convert Text to Date and Time in Excel


Practice Workbook

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

text won't convert to date excel


Download Practice Workbook

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

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

2 Comments
  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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo