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.
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.
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.
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.
Download Practice Workbook
You can download the practice workbook from here:
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
<< 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