It sets date format wrong when copy and paste

Originally Posted by Aardigspook

Ah, sorry, I mis-read the problem (switching day / month is a common problem on here, so I jumped to that conclusion ).

If you are starting from scratch (no dates in Excel yet) then format the sheet as text and use newdoverman's solution above.

If you already have a lot of wrongly-formatted dates, try this:
With the list of films in column A and (wrong) dates in column B, put this in C1 and drag down:

Formula:

=IF (OR (YEAR (B1) < 2013, YEAR (B1) = YEAR (TODAY())),DATE( MONTH (B1), DAY (B1), 1), DATE (YEAR (B1), MONTH(B1), 1))

Then format the cells with the custom format 'mm/yy' (without the inverted commas).
That should do what you want - sorry for my earlier misunderstanding.

Sorry it's taken me a while to reply - I kept being blocked for a technical reason I've now been told how to get round.

The formula won't work. It turns out all years into 2015.

I uploaded the file.

Here;