TEXT DATE Format Conversion

[Guest]

When I have a text file that I open in Excel, the field that brings in the
month is in text as "JAN-07" meaning January 2007. All of my period names
are in this format from the oracle output. Just so I can give you a few more
examples:
JAN-06 is supposed to be January 2006,
FEB-05 is supposed to be February 2005, and
DEC-99 is supposed to be December 1999.

I want to change the format of this field to something that will keep this
relationship because Excel automatically assumes that I want:
JAN-06 to be January 06, 2007,
FEB-05 to February 05, 2007, etc......

It uses the current year to complete the date. I am currently using
"[$-409]mmm-yy;@" as the format to change it to:
JAN-06 to be Jan-07,
FEB-05 to be Feb-07, etc....

Will someone please help me?

 

[Guest]

If you have JAN-07 in A1 as text, then

=DATEVALUE("1/" & LEFT(A1,3) & "/20" & RIGHT( A1,2))

will give you a real date, you should format this cell as a date.