Computer Training & Application Solutions made Simple
Contact us for a free consultation
  • Click to send us an email. We'd love to hear from you!   Ottawa       613-656-4229
  • Click to send us an email. We'd love to hear from you!   Montreal    514-342-9494

Tips for Converting Dates Stored as Text

By Deborah J. Sparks

Often when importing or pasting dates into Excel from another data source, Excel interprets them as text. Depending on the data, there are many ways of dealing with this problem, including the following. If these don't work for you, let us know so that we can provide you with a solution:

1.  Text to Columns:

  • Select the cells and click the Data tab (or menu for Excel 2003 or prior)
  • Click/select the Text to Columns option
  • Select the Fixed Width option and click Next, Next
  • Select the Date option and choose the date format from the drop-down
  • Click Finish to replace the dates or choose a different destination cell to keep the original dates

 

2.  Multiply by 1:

  • Type 1 in a blank cell, select the cell and Copy
  • Select the cells that contain the dates, right-click and select Paste Special
  • Click the Multiply option in the Operation section
  • Delete the 1 value

 

3.  Use the DATEVALUE function:

  • Insert a column to the right of the text dates

  • Assuming the first text date is in cell B2, type the following formula and fill it down the column:

=DATEVALUE(B2)

  • The date serial number will be returned so you will have to format it as a date
  • With the cells still selected, copy and paste values
  • Delete the column that contains the original text dates

 

4.  Use the TEXT Function with the DATEVALUE Function:

  • Insert a column to the right of the text dates
  • Assuming the first text date is in cell B2 and that the format is 15-02-2013 (day-month-year) type the following formula and fill it down the column

=DATEVALUE(TEXT(B2,"dd-mm-yyy"))

  • With the cells still selected, copy and paste values
  • Delete the column that contains the original text dates

 



More Newsletter Articles

© 2013 DJH Training & Application Solutions Inc.

WOULD YOU LIKE TO USE THIS ARTICLE IN YOUR NEWSLETTER, EZINE OR WEB SITE? You can, as long as you include this complete blurb with it: An entrepreneur at heart, Deborah J. Sparks established DJH Training & Application Solutions Inc. over 20 years ago, realizing her dream of creating a company known for providing high quality computer software training, application development and support and exercising her passion for teaching. To learn more and to register for DJH's free newsletter, Innovations, please visit www.djhsolutions.ca.