On Mon, 6 Aug 2012 20:08:57 +0000, ExcelSavior wrote:


I'm working on a data file that has the date input in the wrong format.
In the date column, it shows "13-Jan". The original format intention for
this text is supposed to be "yy-mm", or January 2013, but excel is
automatically reading it as "01/13/2012", and the format is set as
"dd-mm". How do I change the format or text so it reads the original
text correctly as "yy/mm" or "yyyy/mm" as opposed to "dd-mm" or
"mm/dd/yyyy"?

As joeu as pointed out, this is a difficult problem. Here is another solution:

Change the extension of the data file to .txt
Open the file in Excel. The Data Import Wizard should appear.
With regard to the column with the malformed dates, select to import this as TEXT.

You should then see the unaltered data in that column; and all of the entries should be text.
You can then convert it into a string that Excel will recognize as a date; convert it to a true date; and format it as yy-mmm.

For example, with the text in column A:

=--(RIGHT(A1,3) &" 1, " &LEFT(A1,FIND("-",A1)-1)+2000)

or

=--(1 & RIGHT(A1,3) &LEFT(A1,FIND("-",A1)-1)+2000)

will convert it to a date equivalent. You can then format the result as yy-mmm.