Getting Excel to recognize a "mm/yy" entry

I used the clickable link so that you would be able to open it.

Anyway, here is my file. excel sample 1.xlsx

I used the first empty cell (which was E8) to put the formula in.

Thanks for your help

Insert a new column F, by clicking on the F to highlight the complete column and then clicking on <Insert>. This is just a temporary measure, as you will delete column E at the end to restore the structure of your data.

Then put this formula in the new F2:

Formula:

=IF(E2="","",DATE(2000+RIGHT(E2,2),LEFT(E2,FIND("/",E2)-1),1))

Format that cell how you would like it to appear (eg using a Custom Format of m/yy to make it look like the same as E2).

Then copy that formula all the way down to the bottom of your data.

Then highlight column F again, click on <copy>, then right-click and choose Paste Special, then click against Values, then click OK, then press the <Esc> key. This will now have fixed those values, and so you can highlight column E and click on <Delete>.

Then you should be able to sort your data in a meaningful way.

Hope this helps.

Pete