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
Related resources
Replies (3)
Please login or register to join the discussion.
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 /
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.