Excel is a powerful tool for working with dates, but sometimes we may come across data where dates are stored as text.
This can happen due to data import from other systems or manual data entry errors. To perform date-based calculations and analyses, it is important to convert these text-based dates back to actual date formats in Excel.
This article will cover various methods to convert dates stored as text back to dates in Excel.
Method 1: Using the DATEVALUE Function
This function converts a date that’s been stored as text to a serial number that Excel recognizes as a date.
Using the data set below. In this, the dates in column B are stored as text.
We can use the ‘DATEVALUE’ formula to convert it into date format. Follow the below steps to use:
Step 1: Select a blank cell in column C and insert the below formula:
=DATEVALUE(B2)
B2 represents the text-formatted date that you want to convert.
Step 2: Press ‘ENTER’
DATEVALUE function returns the serial number of the date that is represented by the text date, as shown in the screenshot below:
Step 3: From the ‘Home’ ribbon, under the ‘Number tab, you will see that the number format should be General
Step 4: From the drop-down, select the ‘Long -date’ option
Step 5: The serial number will convert into date format.
Step 6: Copy and paste the formula in the remaining cells
NOTE: If you wish to change the format, then you can also use the short date format option from the drop-down.
Or, if you wish to use another format for displaying the date, you can also use ‘Format Cells.
Step 1: Select the cells in question.
Step 2: Right-click and choose Format Cells.
Step 3: Go to the Number tab, select Date, and pick your desired format. Click ‘OK’ once selected
Also read: How to Convert Month Name to Number in Excel?
Method 2: Using the Error Checking
One of the easiest ways to convert text into date is by enabling error checking.
You can check if Error Checking is enabled by looking for a green triangle on the left upper side of the cell where text dates are stored. Refer to the screenshot below:
By resolving Error Checking, you can convert the date stored as text into date format.
Step 1: Select a single cell or range of cells that has an error indicator in the upper-left corner of the cell
Step 2: Click the error button that appears near the selected cell(s).
Step 3: On the menu, click either Convert XX to 20XX or Convert XX to 19XX as per your need
The text dates will convert to standard date format.
You will notice that the Error Checking button, the small triangle in the top-left corner, is no longer visible.
Also read: Get the Day Name from the Date in Excel
Method 3: Using the Text to Columns Feature
For dates in a consistent format, the Text to Columns tool is a handy utility. As the name suggests, it splits texts into different columns with the Convert Text to Columns Wizard.
We will use this functionality to convert text into standard date format by following the below steps.
Step 1: Select the column with text dates.
Step 2: Navigate to the Data tab and click Text to Columns.
Step 3: A dialog box will appear. Select Delimited, and click Next.
Step 4: As we do not wish to split the text into multiple cells, there is no need to define delimiters. Just click on the ‘Next’. Excel will try to identify and convert text to dates.
Step 5: Select ‘Finish’
The text is converted into the date format.
Also read: How to Add Years to A Date in Excel
Method 4: Using the VALUE Function
The VALUE function can convert any text string that looks like a number into a number, so it’s useful for fixing any number, not just dates.
We will use the data set as shown below for this method:
Step 1: Select a blank cell in column C and insert the below formula
=VALUE(B2)
B2 represents the text-formatted date that you want to convert.
Step 2: Excel will return the serial number for your date in column C. Excel stores dates as sequential serial numbers so that they can be used in calculations.
Step 3: You can drag the fill handle or copy and paste the formula. You should have a range of cells with serial numbers that correspond to the range of cells that contain text dates.
Step 4: On the “Home” tab, go to the “Number” group and click on the drop-down showing next to ‘General’ in the tab.
Step 5: Select the “Long Date” OR “Short Date” format from the list of number formats available in the drop-down menu.
NOTE: If you wish to change the format, then you can also use the short date format option from the drop-down.
Or, if you wish to use another format for displaying the date, you can also use ‘Format Cells.
Step 1: Select the cells in question.
Step 2: Right-click and choose Format Cells.
Step 3: Go to the Number tab, select Date, and pick your desired format. Click ‘OK’ once selected.
Also read: Calculate the Number of Months Between Two Dates in Excel
Method 5: Using the Date Function
It’s not uncommon to find problems where we get dots inside a date. This kind of format used by people is not a proper date format and hence is stored as text. Sharing an example below:
The method to correct this is by using the DATE formula. The DATE formula is used in conjunction with the RIGHT, MID, and LEFT functions.
Here is the formula to convert this text into date:
=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))
Explanation of the formula:
The RIGHT function looks at cell A2 and takes the first four characters starting from the very right and moving left. This gives the output “2025” as the year of the converted date in B2.
Below, you will find a detailed explanation of the function argument.
Text represents the text string that contains the characters you want to extract. In this case, it is A2
And Num_chars represents how many characters you want to extract from the right.
In this case, it is 4. This established “2025” as the year in B2
The MID function looks at cell A2. It starts with the 4th character and then takes two characters. This establishes “11” as the month of the converted date in cell B2.
Below, you will find a detailed explanation of the function argument.
Text represents the text string that contains the characters you want to extract. In this case, it is A2
Start_num represents the position of the first character you want to extract. In this case, it is 4
Num_chars represents how many characters you want to extract. In this case, it is 2.
This established “11” as the month in B2.
The LEFT function looks at cell A2 and takes the first two characters from the left. This establishes “17” as the day of the date in the converted date in cell B2.
Below, you will find a detailed explanation of the function argument
Text represents the text string that contains the characters you want to extract. In this case, it is A2
Num_chars represents how many characters you want to extract from the left. In this case, it is 2
This established “17” as the date in B2
Alternate Method:
We can also use an alternate method of Find and Replace. The date is given in the text format below
17.11.2025Step 1: Under the ‘Home’ Tab, look for ‘Find and Select’
Step 2: Click on the drop-down and select ‘Replace’
Note: You can also use the keyboard shortcut. Press Ctrl+H and the dialog box will open
Step 3: Type ‘.’in the column Find What and ‘-‘ in the column Replace with.
Refer to the screenshot below
Step 4: Select Replace All
This will change the format to Date and will also inform how many replacements are made. Refer to the screenshot below.
Conclusion:
In the exciting world of data analysis, nothing is more crucial than the quality and format of your data.
By converting text dates back to their inherent date formats in Excel, you can ensure that your data is consistent and unlock the full potential of Excel’s vast array of date-related functions and calculations.
This precision and clarity in data representation is the key to accurate forecasting, trend analysis, and decision-making.
Depending on your specific needs, you might find that one or a combination of several methods is the perfect solution.
With its powerful toolkit and flexible approach, there’s always a way to optimize, refine, and streamline your data.
Other articles you may also like: