Convert date formats mmm-yy to dd-mm-yy

Hi, I would really appreciate your help with this issue:

I have a column with months and years coded as mmm-yy (eg Sep-07). I want to convert this into numbers to compare with another date field. However, when I do that, excel thinks that Sep-07 (September 2007) is really 7th September, 2012. How can I get around this?

Ps: I have already tried to format the cells as mmm-yy, Datevalue, multiplying the column by 1 and pasting, using text to date and none of these seem to work.

Thanks so much for your help!

I was thinking

=DATE(2000+RIGHT(A2,2),MATCH(LEFT(A2,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Dec"},0),1)

That gives me an error (zbor)...

Thanks Cutter, but again, it does not seem to be working - maybe because there is a hyphen between LEFT and RIGHT (Sep-07)?

The formula works on "Sep-07". Might there be leading or trailing spaces in your data?

Originally Posted by Pepe Le Mokko

Is Sep-07 a text or a format?

It is in text currently. I tried to convert it to "custom" mmm-yy format but that does not seem to work.

And the answer to:

Might there be leading or trailing spaces in your data?

is.............?

Originally Posted by Cutter

And the answer to:

is.............?

There are no spaces before or after the mmm-yy entries. I will post a sample shortly. Tanks for all your help guys!