Another way to convert text to dates is by adding zero to the text. This is a trick which forces Excel to convert the text into an Excel value so it can then add the zero. Since adding zero doesn’t change the value, you end up with text converted to an Excel value.
It’s a long-standing trick for converting text into values, not just dates but also numbers.
However, it has the same problems as using DateValue() or Value() for date conversion. We mention it for the sake of completeness, rather than a good alternative.
- Select the text cells and copy to clipboard with Ctrl + C
- Place the cursor in the first cell of the pasting destination
- Choose Home | Clipboard | Paste | Paste Special then Operation: Add.
Operation add/subtract/multiply/divides the value in the destination cell with the pasted value. In this case all the destination cells are blank so Zero is added.
Note: the Excel ‘plus zero’ conversion trick works with Paste Special | Add to blank cells or in a formula as ‘+0’ (eg =A2+0 ) but does NOT work with the Sum() function.
Here’s the result with the results as numbers:
Change the cell formatting of column B to a date format.
As you can see, there’s the same conversion problems as DateValue() and Value(). Converting text numbers to Excel values should be more accurate.
Text to Excel Date conversion by adding Zero with Paste Special
Simple text with day, month and year to Excel date conversion
Converting Text with month and year into Excel dates
