Share this content

text to date in excel

have an excel sheet containing a summary of a lot of word file, tables data which has been cut and pasted into excel

most of the data is fine to filter and pivot, ...  but one column of this data is a 'date column' entered in word as 12.10.09 format.  this will not convert to 12-OCt-09 using the format option in excel.  my question therefore is how to convert this colunm into the required date format  ie from 12.10.09 to 12-oct-09

many thanks


Please login or register to join the discussion.

By ACDWebb
03rd Dec 2010 10:04

I suspect it is the full stop delimiters

that is confusing Excel based on your system preferences

How about doing a Find & Replace (Ctrl+h) searching for . and replacing with /

Thanks (0)
03rd Dec 2010 10:36

job done


brilliant thanks.... works a treat


Thanks (0)
07th Dec 2010 12:30

Text to Columns wizard


Highlight your dates column and run the "Text to Columns" wizard on the Data ribbon. 

Step 1: specify that your entries are fixed width

Step 2: click next

Step 3: specify that your entries are dates

I posted on this a while ago in connection with dates imported to Excel from SAGE - Excel does not always recognise SAGE dates as dates.  Works here too.


Thanks (0)