Perhaps the 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.
Office Watch has the latest news and tips about Microsoft Office. Independent since 1996. Delivered once a week.