Dates, in particular, can be difficult to convert into true date values if they aren’t formatted correctly. If you’re faced with this problem, there are a few ways you can convert text to date values in Excel. Here’s what to do.
Using the VALUE Function
When your cell contains a date but Excel thinks it’s a text string, you can (in most cases) use the VALUE function to convert it. This takes the text value and converts it into a five-digit Unix timestamp, which can then be converted into a typical date value that you’d visually recognize. You can use VALUE to convert any text string to a number. Excel, in most cases, is able to recognize a likely date value and, using VALUE, it can convert it to a date value instead. To convert a text string to a date value, type =VALUE(A2), where cell A2 contains the text string that you’re converting. In the example below, we’re using the day-month-year date format, but this will work for other date formats, too.
This only works for certain types of likely date values. As you’ll see from the image above, it doesn’t support dates that use decimal places (for instance, 12.05.1990). It will return a #VALUE error for cells it can’t convert. Once you have a converted Unix timestamp, you can change this using Excel cell formatting by selecting the date cells and selecting the Date option from the Number drop-down menu in the Home tab.
Using the DATEVALUE Function
In most cases, using VALUE and converting the result to the date cell type will return a recognizable date value. If it doesn’t, then you can use the DATEVALUE function to convert the text string to a date value instead. To do this, type =DATEVALUE(A2), where A2 contains the date value as a text string. It can also be used to convert text directly. To do that, type =(DATEVALUE(“12/05/1990”) and replace the date value with a text string of your choice instead.
As you’ll see from columns B and C in the image above, formulae using the DATEVALUE function will return a Unix timestamp. You’ll need to select these cells and convert them to the date cell format by selecting Date from the Home > Number drop-down menu.
How to Convert Decimal Date Values
In almost all cases, VALUE and DATEVALUE will convert text strings to date values. An exception, as you’ve seen above, is with dates that use decimal point delimiters to separate day, month, and year values. You can use the Excel Find and Replace tool to convert these, but an easier method is to use the SUBSTITUTE function. This will change all decimal point delimiters to forward slashes, which both VALUE and DATEVALUE can then recognize. To do this, type =SUBSTITUTE(A2,”.”,”/”), where A2 contains your date value as a text string, “.” is selecting the decimal point delimiter to search for, and “/” is replacing all examples of the decimal point with a forward slash.
In the example above, the DATEVALUE function used in cell B2 cannot convert the original text string in A2. The SUBSTITUTE function used in C2, however, has replaced all decimal point delimiters with forward slashes. A further DATEVALUE function used in D2 is then able to convert the new text string from C2 into a correct date.
Converting Text to Date Cells in Excel
Using the tools above to convert text to date cells in Excel will allow you to properly use date values within your Excel spreadsheet. You can take things a step further by cross-referencing that data across Excel spreadsheets. If that sounds complicated, start at the beginning with some beginner Excel tips that every user should get to grips with.
