Excel 2016 Custom formatting 18/04 YY/MM

Hi I wonder if anyone can help me and my work colleague please?

We have a spreadsheet that we consolidate data from 2 "live" working documents, just copy and paste.

But it would appear it Excel doesn't like 18/04. The sheet dates back to 2016. The column is formatted - Custom - YY/MM, and right up to and including 18/03, the field as you see it says 18/3 and the formula bar he same.

Yet as soon as the month changes to 18/04, it changes it to a full date (18/4/2018). So filters no longer list it with the other data, its 2018 - April - 18 and below it are listed the new entries.

I have tried formatting with the format painter, even dragged down 2 of the correct fields showing 18/03, the minute I try to change the 3 or 4, 18/04, it goes back to date again. Even if I select some fields within that column it shows that it is custom formatted.

The minute I mouse click within any of the correctly formatted months, it too changes to a date (under filters there are now 2 entries under 2018.

Please can somebody help us? We have tried various google searches but haven't found a solution that works.

I have tried 18/05 and that displays as it should why does Excel not like 18/04?

I've cut my sheet right back, I have it saved it on Dropbox.

https://www.dropbox.com/s/jtc38hbb41...2018.xlsx?dl=0

Thank you
Clair

Thank you, you are a star :-)

Clair

If you have 18/3 in the formula bar you don't have a date, you have text, it doesn't matter what the format is.

Try formatting the entire column as Text, then you should be able to enter your 'dates' as YY/MM.

Or, if you really want dates in the column you could convert the existing data.

Thank you both :-)