Try using:

=DATE(20 & RIGHT(D2,2),LOOKUP(MID(D2,4,3),Table!$A$1:$B$12),L EFT(D2,2))

If I have amended your formula correct you should not be passing 2005 rather
than 05 as the year, thus taking out any ambiguity.



"Shatin" wrote in message
...
I have a spreadsheet with data generated by another program. There is one
column with dates in this format: 04-May-05. From what I can tell, Excel
is
treat this as a string, not a date.

Next to this column, I use the following formula to convert a date such as
04-May-05 into 04-09-05 (this new column is formatted as yy-mm-dd).

=DATE(RIGHT(D2,2),LOOKUP(MID(D2,4,3),Table!$A$1:$B $12),LEFT(D2,2))

The lookup is for changing Jan into 01, Feb into 02, etc.

PROBLEM:

When I do a pivot table with the column, a date such as 04-May-05 appears
as 1904-09-05, rather than 2004-09-05. What should I do to make sure that
the years are 20xx and not 19xx?

In fact, is there a better way to do the date conversion? My method seems
rather clumsy.