Getting Excel to recognize a "mm/yy" entry

I have a dataset that looks like this (image attached, I hope). excelscreenshot.png

I think that Excel is currently recognizing this as text rather than numbers. These numbers are dates, with the digits before the slash referring to a month and the digits after the slash referring to a year. I cannot get excel to recognize this. I have spent an hour or so messing around with "format cells" but the only thing Excel wants to do is attach a "2012" on the end of my dates -- EG rather than realizing I mean "June 2008" in the first row, it thinks I mean "June 8, 2012."

I think the problem is compounded by the fact that some of these dates have a leading zero (like 06/08) and some don't (9/10). When I try to standardize this, Excel again jumps on adding a "2012" to everything.

I would greatly appreciate any suggestions that don't involve me manually editing 15,000 cells Thanks in advance.

With my European date set up the following works

= DATEVALUE("1/" & A1) in B1 and format mm/yy

For the US format you might try

=DATEVALUE(LEFT(A1,FIND("/",A1)) & "1/" & RIGHT(A1,LEN(A1)-FIND("/",A1)))

Here is an example of my data copied & pasted. I just want to be able to sort oldest to newest & have excel properly recognize that I mean month and year, not date/month.


06/08
9/10
7/13
9/09
1/11
3/14
4/11
6/11
07/06
5/09
4/12
10/07
5/10
6/13
06/07
11/06
05/08
9/09

Thanks. Where do I type this in?

Thanks, but I'm completely lost regarding what you're saying. This is what my data looks like -- http://i.imgur.com/UcHVH.png -- but it extends for 15,000 rows. The column I am trying to fix is E. Right now Excel has no idea that those are dates (mm/yy) and so when I try to sort, it doesn't work. I just want Excel to treat these entries as mm/yy dates.

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