People of all ages and professions cannot but know Microsoft Excel today, it is a program for creating spreadsheets to work with data. Someday is important to any analysis of data since we are required to measure data trends appropriately. However, there’s a problem here: when entering dates in Excel in the wrong format, the program then recognizes it as text.
Therefore, you can use it further to do other calculations, but there is no way you will do the other calculations. The good news is that there are ways that such a text can be converted to date in Excel and that’s what this write-up seeks to explore. Thus, if you have been asking yourself how you spill text to date in Excel then the answer is as follows.
What Are the Differences Between Regular Date and Text Date?
Microsoft Excel has two date formats: regular date and text date. Excel identifies the standard date and allows you to carry out various operations with dates as well as use the related Date functions. Text date on the other hand is a form of date format that Excel doesn’t recognize as date that you can manipulate by calculations or edits, instead, it is read as a text string.
So, Excel behaves differently towards these two formats in the following ways:
1. Sorting
When sorting, Excel automatically sorts regular dates ascending by date while text dates by the alphabet. For example, regular dates will be sorted as follows:
01/01/2023
02/01/2023
03/01/2023
On the other hand, text dates are sorted as follows:
01/01/2023
01/02/2023
01/03/2023
Fourth unless you Excel convert text to date dd/mm/yyyy then you will continue to encounter those problems.
2. Calculations
In Excel, the way to add or subtract the number of days between two days is very easy. The text date format in contrast does not allow calculations to be made in such a way.
3. Date Functions
It is ideal to use regular date formats while data manipulation intends for different date functions including TODAY(), MONTH() as well and YEAR(). In turn, text-based dates cannot simply work with these functions.
Now, it is time to provide insights as to how to effectively convert text to date in Excel.
Convert Text to Date in Excel Via Paste Special?
Paste Special, is a method of how to convert text to date in Excel, it involves the addition of a zero to the text. Doing it makes Excel interpret as it reads the text rather than change the text in the formula because adding a zero is just adding a zero.
For instance, the following from which we need to transform text to date in Excel =>
You need to follow these steps to convert text to date in Excel:
Step 1: If you have a new Worksheet opened in front of you now, it’s enough to copy any empty cell.
Step 2: Copy the text dates on the left side to make them regular dates.
Step 3: Now that you have chosen the left section, right-click on the list and select paste special from the list you will be presented with a paste special dialog box.
Step 4: Choose the Values option under the Paste commands section.
Step 5: It is under the ‘Operations’ tab, click on ‘Add’.
Step 6: Lastly, for all your text dates, your only option will be ‘Ok’ and your output will be obtained in numeric values.
Step 7: After following the above steps you are in a position to format this and display any of these numbers as dates by selecting Short Data or Long Date.
Change Text to Date in Excel Via DATEVALUE?
Extracting your data from a database, or the web, changes ordinary dates into string dates. But do not worry; you can change the text to date in Excel using the DATEVALUE function. It translates the text data into a numerical value which you can proceed to format in a date.
To illustrate, here is the dataset that we are going to work with, and in Column A, use the Excel formula to convert text to date.
Step 1: They are to enter the formula =DATEVALUE(A2) And then copy this formula along the cells within column B.
You can see that using the formula provides you with a number in front of the text date.
Step 2: To convert the numeric values into dates the date value results should be selected in the cells then go to the Home tab.
Step 3: Pull down the formatting drop-down and choose the Short or Long Date format.
Step 4: After finishing the above 3rd step you will notice the text date format appearing as normal dates.
Converting Text to Date in Excel Via VALUE
Just as was seen in the other methods that were discussed earlier, the VALUE functions are also beneficial in converting text to date in Excel. Unlike the DATEVALUE function, it can take dates as well as other entries into account.
In this case, let’s use the same dataset as in the other examples and determine how we can convert fields to dates with the help of the VALUE function.
There it is used as =VALUE(A2).
Follow these steps to convert text to date in Excel:
Step 1: Click ‘Ctrl + C’ to copy the above formula, then right-click the cells in column B to paste, thereby providing you with numeral values. You can convert these numbers to dates in the later steps.
Step 2: Now choose the numerical values only and on the Home tab,
Step 3: On the right side, one has to click the drop-down menu Choose Format > Number > Currency > {C#,###,##0” Short } or {C#,###,##0$” Long }.
Change Text to Date Format in Excel Via Text to Columns
Converting from text to date in Excel is also possible through text to columns with any separator to date. Here is the dataset that we are going to convert to the regular dates format.
Follow these steps to convert text to date in Excel:
Step 1: On the top of the screen, click the Data tab. Then hit Data Tools Group. Last, choose Text to Column options.
Step 2: When you complete this step, you will see a Convert Text to Columns Wizard, and you will need to complete 3 steps. Then click on Delimited and after that click on the Next button.
Step 3: Select no delimiter and press Next.
Step 4: Choose any Date format that you would wish to use. Next, enter the cell name you wish for your Destination Box for the cell that you’ll be putting in. Finally hit Finish.
Result: To check the conversion you will have to look at the Real Dates column and you’ll see all the converted dates.
