When importing data into Excel, it's common to encounter dates formatted as text. These text dates can't be used in calculations or data analysis until they're converted into proper date formats recognized by Excel.
Excel offers several methods to transform text entries into usable dates. Below are the most effective techniques to accomplish this conversion.
Convert Text to Date Using the Error Checking Feature
Excel's built-in error checking can automatically detect cells that might contain dates stored as text. When Excel suspects an error, it displays a small green triangle in the upper-left corner of the cell. Selecting the cell reveals a caution icon; hovering over it shows a message explaining the potential issue.
For instance, if a date is entered with a two-digit year, Excel may interpret it as text. Clicking on the caution icon provides options to convert the text to a date format. Choose the appropriate conversion, such as changing "15" to "2015" or "1915".
After selecting the correct option, Excel converts the text into a proper date format.
Enabling the Error Checking Option in Excel
If the error checking feature isn't active, you can enable it in Excel's options.
Step 1: Click on the File tab and select Options from the menu.
Step 2: In the Excel Options window, navigate to the Formulas tab. Under the Error Checking section, ensure that Enable background error checking is checked. Also, check the option for Cells containing years represented as 2 digits under Error checking rules.
Convert Text to Date Using the Text to Columns Feature
The Text to Columns tool is a versatile feature that not only splits text into multiple columns but can also convert text to dates. This method is particularly useful when dealing with dates in a consistent format.
Converting Simple Text Strings to Dates
Consider a scenario where your dates are formatted as text strings like "01 02 1995". Here's how you can convert them:
Step 1: Select the range of cells containing the text dates.
Step 2: Go to the Data tab and click on Text to Columns in the Data Tools group.
Step 3: In the Text to Columns wizard, choose Delimited and click Next.
Step 4: Uncheck any delimiters and click Next.
Step 5: In the final step, select Date under Column data format. Choose the appropriate date format from the drop-down menu (e.g., DMY for "day month year") and click Finish.
Your text dates are now converted into proper Excel date formats and should be right-aligned in the cells.
Note: Ensure that all text dates are in the same format before using this method. If formats vary (e.g., some in DMY and others in MDY), the conversion may produce incorrect results.
Converting Complex Text Strings to Dates
For more complicated text strings, such as "Wednesday, February 01, 2020" or "February 01, 2020, 4.10 PM", the Text to Columns feature can help by splitting the text into separate components.
Step 1: Select the cells containing the complex text dates.
Step 2: Click on Text to Columns under the Data tab.
Step 3: Choose Delimited and click Next.
Step 4: Select the delimiters present in your text (e.g., comma and space) and click Next.
Step 5: In the final step, select General as the column data format for all columns. Specify a destination if you don't want to overwrite the original data, and click Finish.
The text is now split into multiple columns containing days, months, years, and times.
To reassemble the date, use the DATE function along with the MONTH function to convert month names to numbers.
=DATE(Year, MONTH(1 & Month), Day)For example, if the year is in column E, the month name in column C, and the day in column D, the formula would be:
=DATE(E1, MONTH(1 & C1), D1)
Drag the fill handle to apply the formula to the remaining cells.
Convert Text to Date Using the Paste Special Tool
Another straightforward method involves using the Paste Special feature to add zero to your text dates, effectively converting them into date serial numbers.
Step 1: Select an empty cell and copy it by pressing Ctrl + C.
Step 2: Select the cells containing the text dates you wish to convert. Right-click and choose Paste Special.
Step 3: In the Paste Special dialog box, select All under Paste and choose Add under Operation. Click OK.
The text dates are converted to date serial numbers.
Step 4: Select the cells with the serial numbers. Go to the Home tab, click on the Number Format drop-down, and choose Short Date.
The serial numbers are now formatted as dates.
Convert Text to Date Using Find and Replace
If your text dates use delimiters that Excel doesn't recognize (such as periods instead of slashes), you can use Find and Replace to update them.
Step 1: Select the cells containing the text dates.
Step 2: Press Ctrl + H to open the Find and Replace dialog box.
Step 3: In the Find what field, enter the existing delimiter (e.g., a period "."). In the Replace with field, enter a recognized delimiter such as a slash "/". Click Replace All and then Close.
Excel now recognizes the dates and converts them accordingly.
Convert Text to Date Using Formulas
Excel provides functions like DATEVALUE and VALUE to convert text to dates.
Using the DATEVALUE Function
The DATEVALUE function converts a date represented as text into a serial number that Excel recognizes as a date.
=DATEVALUE(date_text)For example, if cell A1 contains a text date, the formula would be:
=DATEVALUE(A1)After applying the function, format the cells to display dates.
Step: Select the cells with the serial numbers, go to the Home tab, and choose Short Date from the Number Format drop-down.
The cells now display the dates in a recognizable format.
Using the VALUE Function
The VALUE function can convert text strings that resemble dates or numbers into numeric values.
=VALUE(text)For example, to convert a text date in cell A1:
=VALUE(A1)
After getting the serial numbers, format the cells as dates using the Number Format options.
Converting text to dates in Excel is essential for accurate data analysis. By using these methods, you can ensure that all your date entries are correctly formatted and ready for calculations.
Member discussion