I am trying to populate a pivot with expiery dates presented in US fomat
mm/dd/yyyy. Due to default settings, which I am unable to change, it
currently reads the dates as UK dates dd/mm/yyyy, and therefore presents
those with mm value 12 as text. I used
=DATE(RIGHT(J10,4),LEFT(J10,2),MID(J10,4,2)) on the second scenario, and it
then presents these dates in UK format, but now this doesn't work on the
dates with month values < 12.

Any help on this will be appreciated.