How to convert text to date in Excel (Easy Formula)
A free Office suite fully compatible with Microsoft Office
Excel typically does a decent job of recognising cell data. It frequently formats text-based cells as text, numeric cells as numeric cells, date cells as date cells—you get the point. Even if you've copied the content from another source, this doesn't always work.
If dates aren't structured properly, it might be challenging to transform them into accurate date values. There are a couple methods you can convert text to date data in Excel if you run into this issue. Here is what to do.
You can analyse text and put together a valid date with a formula based on the functions DATE, LEFT, MID, and RIGHT to convert text in an unfamiliar date format to a proper Excel date. The formula in C6 in the illustrative case is:
The DATE function is used in this formula to combine the year, month, and day variables to generate the date January 15, 2004.
Using the three variables year, month, and day, the DATE function generates a valid date:
We extract each of these parts from a text string in cell C6 using the LEFT, MID, and RIGHT functions, and then use the results to feed the DATE function:
The LEFT function extracts the first 4 characters from the left to represent the year, the MID function extracts the first 5–6 characters to represent the month, and the RIGHT function extracts the last 2 characters to represent the day. Direct returns of each result are made to the DATE function. The outcome is a legitimate Excel date that may be formatted anyway you'd like.
This strategy can be altered as necessary. For instance, row 8's unrecognised date format is dd.mm.yy. The formula in C8 is:
Sometimes dates in a lengthier format, such as Feb 27 2022 05:13:08, may exist that Excel is unable to correctly identify. In this situation, you might be able to modify the string in a way that enables Excel's SUBSTITUTE function to accurately identify the date. The formula below substitutes a comma and space (, ) for the second instance of a space ( ).
=SUBSTITUTE(A2, ,, ,2)+0 // add comma after month
Excel will recognise the date after we add the comma after the month name, but it still needs a little kick. We add zero at the end because of this. Excel attempts to translate the text into a number as a result of the math operation. A valid Excel date will be produced if this is successful. To show the date accurately, you might need to use date number formatting.
Try one of the solutions listed below before using a formula to manually parse and create a date from text. The first method nudges Excel a little and forces it to attempt evaluating the text as an integer.This frequently works since Excel dates are in reality numbers. If the procedure is successful, you might need to use a date format.
To fix dates, add zero
You may occasionally come across dates in a text format that Excel ought to be able to read. By adding a zero to the value, you might be able to compel Excel to turn the text values into dates in this situation. Excel will attempt to convert text values to integers when you add zero. Dates are essentially numbers, therefore using this method to convert dates to a text format that Excel can really understand is a wonderful idea.
Try Paste Special to Convert Dates in Place by Adding Zero.
1.In an empty cell, type 0 and then click Copy to Clipboard.
2.Pick the questionable dates.
3.Special > Paste > Values Add
4.Use a date format (if needed)
5.Zero can also be added to the following formula:
A1 has an unknown date, hence the equation is
To adjust dates, add text to columns
Using the Text to Columns Feature is another technique to allow Excel to detect dates:
Choose the dates column, then select Data > Text to Columns > Fixed > Finish.
If Excel is able to detect the dates, it will correct them all at once.
Did you learn about how to convert text to date in Excel? You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets and PowerPoint Slides.
You can also download WPS Office to edit the word documents, excel, PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience
Was this helpful?
- 1. How to get month name from a date in Excel (3 easy ways)
- 2. How to compare two excel sheets and highlight differences
- 3. Check if value is in list in Excel (3 easy methods)
- 4. How to copy file names in Excel from a folder?
- 5. How to color cell based on value in Excel?
- 6. How to add month name from date in Excel formula