top of page

Microsoft Excel articles et conseils

Excel Icon.png
Back Button

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 > Text to Columns option

  • Select the Fixed Width option and click NextNext

  • 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 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

© 2013 DJH Training & Application Solutions Inc.

bottom of page