Quote:
Originally Posted by joeu2004[_2_]
"Ron Rosenfeld" wrote:
As joeu as pointed out, this is a difficult problem. Here is another
solution:
[....]
=--(RIGHT(A1,3) &" 1, " &LEFT(A1,FIND("-",A1)-1)+2000)
Well, if we're going to make the assumption that all dates are in the year
2000 or later, it is not difficult at all to provide a region-independent
solution.
Since we're making unsubstantiated assumptions, let's assume the dates are
in the years 2001 through 2028.
Then the following should work, assuming that yy-mmm data are already input
and interpreted as dd-mmm of the current year (i.e. numeric Excel dates; the
display format does not matter).
Enter the following formula into X1 and copy down through X1000:
=DATE(2000+DAY(A1),MONTH(A1),1)
Copy X1:X1000 and use paste-special-value to overwrite A1:A1000. Delete
X1:X1000. Format A1:A1000 any way you wish.
|
This works exactly as intended. Thank you so much. Now I have one issue - although the date format is correctly stated, once I change it to text format, it goes into that weird coding Excel does for dates. for example, a date of "201305" in text become "41395". What I want to do is add an "F" in front of "201305" and get a result of "F201305" - this is my final product. Any ideas on how to get the text to read as a regular date?