The simple and easiest way to convert text dates to Excel dates is using Text to Columns on the Data tab.
It’s a simple ‘one off’ method that works well and takes you through the steps.
If you need something more automated, for new text dates entered from time-to-time use one of the formulas at Converting Text with month and year into Excel dates
Text to Columns
It’s a wizard that will take you through the steps for converting any text with consistent separators.
- Select the list of text cells
- Go to Data | Text to Columns
- Choose ‘Delimited’
- None of the menu items apply but the dash will appear in the ‘Text Qualifier’ list. Make sure Excel has selected correctly before proceeding.
- Choose ‘Column data format’ as Date then the correct order of day, month, year from the list.
Check the Data preview window to see the results.
Destination: the default is to overwrite the selected cells. Change that if you want to keep the text cells.
Click Finish to end and Voila! There are your dates converted into Excel values. Use one of the cell formats to show the dates in whatever form you choose.
Five ways to turn text into Excel dates
Text to Excel Date conversion by adding Zero with Paste Special
Converting Text with month and year into Excel dates
The hidden trap in Excel’s DateDif()
Calculate age and other interesting facts using Excel and DateDif()
Dates, time and duration, the truth in Excel