Date Problem from Text

[Annette]

I'm having a date problem. I bring over copied text from a mainframe in
simple text format into Excel and paste. It ends up in one row per line
which is fine. The first cell is a date ... the problem field.

That date is mm/yy and I'm running into problems with Excel and how it
treats it when I convert text to columns.

Any of the dates that have mm/01 ... get converted to this year (mm/04 and
are displayed as such: 1-MMM). I can't have it changing the date and I have
pulled out my hair trying to stop this.

Here's a sample of what pulls over:
06/99 FROGS 130.00
0539199J 2020693D

05/01 TOADS 120.00
0539333J 1020613D


When these columns get converted, the 05/01 turns into 05/01/2004 which is
not correct ... it should be 2001. If I put an ' in front of the date, it
will convert correctly, but I can't manually put that in as there are too
many rows ... can someone help or is there some code someone has written
that would resolve this?

Thanks!

 

[kkknie]

I've had this problem as well. My solution was to select Text as th
Column Data format for the column. Then insert a column and create th
proper date. Something like:

=LEFT(TRIM(A1),2)&"/01/"&RIGHT(TRIM(A1),2)

You may have to format the new column first as mm/yy. I even had t
add another column and simply add 0 to the current column to get th
format to stick.

 

[Tom Ogilvy]

Why not just loop through your data and add a day to it

change 06/99 to 01/06/99

as an example. Or change it to mmm-yy ex: Jan 99

Then excel won't try to interpret it.