Simple text with day, month and year to Excel date conversion

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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.

  1. Select the list of text cells

  2. Go to Data | Text to Columns
  3. Choose ‘Delimited’

  4. None of the menu items apply but the dash will appear in the ‘Text Qualifier’ list. Make sure Excel has selected correctly before proceeding.

  5. 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.

 

Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address