Convert Text To Date In Excel
Edel Dieringer
The tutorial explains how to use Excel functions to convert text to date and number to date, and how to turn text strings into dates in a non-formula way. You will also learn how to quickly change a number to date format.
Since Excel is not the only application you work with, sometimes you'll find yourself working with dates imported in an Excel worksheet from a .csv file or another external source. When that happens, chances are the dates will export as text entries. Even though they look like dates, Excel won't not recognize them as such.
There are many ways to convert text to date in Excel and this tutorial aims to cover them all, so that you can choose a text-to-date conversion technique most suitable for your data format and your preference for a formula or non-formula way.
When importing data into Excel, there is often a problem with date formatting. The imported entries may look like normal Excel dates to you, but they don't behave like dates. Microsoft Excel treats such entries as text, meaning you cannot sort your table by date properly, nor can you use those "text dates" in formulas, PivotTables, charts or any other Excel tool that recognizes dates.
As you probably know, Excel stores dates and times as serial numbers and it is only a cell's formatting that forces a number to be displayed as a date. For example, 1-Jan-1900 is stored as number 1, 2-Jan-1900 is stored as 2, and 1-Jan-2015 is stored as 42005. For more information on how Excel stores dates and times, please see Excel date format.
When calculating dates in Excel, the result returned by different date functions is often a serial number representing a date. For example, if =TODAY()+7 returns a number like 44286 instead of the date that is 7 days after today, that does not mean the formula is wrong. Simply, the cell format is set to General or Text while it should be Date.
To apply a format other than default, then select the cells with serial numbers and press Ctrl+1 to open the Format Cells dialog. On the Number tab, choose Date, select the desired date format under Type and click OK.
To convert such a number to date, you will have to use the DATE function in combination with RIGHT, LEFT and MID functions. Unfortunately, it is not possible to make a universal formula that will work in all scenarios because the original number can be input in a variety of different formats. For example:
Note. The formulas demonstrated in this example work correctly as long as all numbers you want to convert to dates follow the same pattern.How to convert text to date in ExcelWhen you spot text dates in your Excel file, most likely you would want to convert those text strings to normal Excel dates so that you can refer to them in your formulas to perform various calculations. And as is often the case in Excel, there are a few ways to tackle the task.
Compared to DATEVALUE, the Excel VALUE function is more versatile. It can convert any text string that looks like a date or number into a number, which you can easily change to a date format of your choosing.
The Excel VALUE function can process both date and time, the latter is converted to a decimal portion, as you can see in row 6 in the following screenshot:
Mathematical operations to convert text to datesApart from using specific Excel functions such as VALUE and DATEVALUE, you can perform a simple mathematical operation to force Excel to do a text-to-date conversion for you. The required condition is that an operation should not change the date's value (serial number). Sounds a bit tricky? The following examples will make things easy!
As you can see in the above screenshot, mathematical operations can convert dates (rows 2 and 4), times (row 6) as well as numbers formatted as text (row 8). Sometimes the result is even displayed as a date automatically, and you don't have to bother about changing the cell format.
How to convert text strings with custom delimiters to datesIf your text dates contain some delimiter other than a forward slash (/) or dash (-), Excel functions won't be able to recognize them as dates and return the #VALUE! error.
Now, the DATEVALUE or VALUE function should have no problem with converting the text strings to dates. In the same manner, you can fix dates containing any other delimiter, e.g. a space or a backward slash.
As you see, the Excel DATEVALUE and VALUE functions are quite powerful, but both have their limits. For example, if you are trying to convert complex text strings like Thursday, January 01, 2015, neither function could help. Luckily, there is a non-formula solution that can handle this task and the next section explains the detailed steps.
Text to Columns wizard - formula-free way to covert text to dateIf you are a non-formula user type, a long-standing Excel feature called Text To Columns will come in handy. It can cope with simple text dates demonstrated in Example 1 as well as multi-part text strings shown in Example 2.
Now, Excel recognizes your text strings as dates, automatically converts them to your default date format and displays right-aligned in the cells. You can change the date format in the usual way via the Format Cells dialog.
Note. For the Text to Column wizard to work correctly, all of your text strings should be formatted identically. For example, if some of your entries are formatted like day/month/year format while others are month/day/year, you would get incorrect results.Example 2. Converting complex text strings to datesIf your dates are represented by multi-part text strings, such as:
And finally, have a look at the Data preview window and verify if the text strings are split to columns correctly, then click Next.
- On step 3 of the wizard, make sure all columns in the Data Preview section have the General format. If they don't, click on a column and select General under the Column data format options.Note. Do not choose the Date format for any column because each column contains only one component, so Excel won't be able to understand this is a date.If you don't need some column, click on it and select Do not import column (skip).
As you see in the screenshot above, we are skipping the first column with the days of the week, splitting the other data into 3 columns (in the General format) and inserting these columns beginning from cell C2.
- Finally, you have to combine the date parts together by using a DATE formula. The syntax of the Excel DATE function is self-explanatory:DATE(year, month, day)In our case, year is in column E and day is in column D, no problem with these.
What you have just done is tell Excel to add a zero (empty cell) to your text dates. To be able to do this, Excel converts a text string to a number, and since adding a zero does not change the value, you get exactly what you wanted - the date's serial number. As usual, you change a number to the date format by using the Format Cells dialog.
To learn more about the Paste Special feature, please see How to use Paste Special in Excel.
Fixing text dates with two-digit yearsThe modern versions of Microsoft Excel are smart enough to spot some obvious errors in your data, or better say, what Excel considers an error. When this happens, you will see an error indicator (a small green triangle) in the upper-left corner of the cell and when you select the cell, an exclamation mark appears:
How to change text to date in Excel an easy wayAs you see, converting text to date in Excel is far from being a trivial one-click operation. If you are confused by all different use cases and formulas, let me show you a quick and straightforward way.
That's it! The results of conversion will appear in the adjacent column, your source data will be preserved. If something goes wrong, you can simply delete the results and try again with a different date order.
Tip. If you chose to convert times as well as dates, but the time units are missing in the results, be sure to apply a number format that shows both the date and time values. For more info, please see How to create custom date and time formats.If you are curious to learn more about this wonderful tool, please check out its home page: Text to Date for Excel.
This is how you convert text to date in Excel and change dates to text. Hopefully, you have been able to find a technique to your liking. In the next article, we will tackle the opposite task and explore different ways of converting Excel dates to text strings. I thank you for reading and hope to see you next week.
Hi! Extract the desired data from the text string and create a date using the DATE function.
You can find the examples and detailed instructions here: Excel substring: how to extract text from cell.
Based on the information provided, the formula could look like this:
I recommend paying attention to the Text to Date tool. This tool easily converts text strings of different formats to dates. The tool is included in the Ultimate Suite for Excel and can be used in a free trial to see how it works.
Many thanks for the article and indicating that datevalue won't work with any delimited other than "/". I was using German Regional Format, which has "." as the date delimiter and Mar2023 used to datevalue was throwing error. Now I have a solution either to change settings to English or use substitute function in case the cell referred to has "." in it.
This formula =SUMIF(AN31:AR31, ">TODAY()", AN30:AR30) always results in ZERO (0) even though row 31 has dates in date format and row 30 has numerical format numbers. Putting the actual TODAY date in place of the TODAY function gives the correct result. Can someone tell me why my formula doesn't work with the function criteria.
Here are some examples of the data I am trying to convert from 8 digits to yyyy/mm/dd. I have tried the formula listed above from this site and it only allowing me to input one cell on the column. Is there a formula to convert one cell and apply formula to all 8 digit dates listed in column?
d3342ee215