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

img 5be017b7d0d5b - Simple text with day, month and year to Excel date conversion

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

    simple text with day month and year to excel date conversion microsoft excel 24395 - Simple text with day, month and year to Excel date conversion

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

    simple text with day month and year to excel date conversion microsoft excel 24396 - Simple text with day, month and year to Excel date conversion

  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.

    simple text with day month and year to excel date conversion microsoft excel 24397 - Simple text with day, month and year to Excel date conversion

  5. Choose ‘Column data format’ as Date then the correct order of day, month, year from the list.

    simple text with day month and year to excel date conversion microsoft excel 24398 - Simple text with day, month and year to Excel date conversion

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.

simple text with day month and year to excel date conversion microsoft excel 24399 - Simple text with day, month and year to Excel date conversion

 

subs profile e1563205311409 - Simple text with day, month and year to Excel date conversion
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