kiwilondon99
Blogger
Share this content
0
3
3810

text to date in excel

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

Replies

Please login or register to join the discussion.

avatar
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)
avatar
03rd Dec 2010 10:36

job done

 

brilliant thanks.... works a treat

 

Thanks (0)
avatar
07th Dec 2010 12:30

Text to Columns wizard

Alternatively...

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)