You could try something like this:

=IF(ISNUMBER(J10),DATE(YEAR(J10),DAY(J10),MONTH(J1 0)),DATE(RIGHT(J10,4),LEFT(J10,2),MID(J10,4,2)))

So, if it has already been recognised as a date then swap the day and
the month around, otherwise use your formula.

Hope this helps.

Pete

On Jul 29, 11:31*am, Labuscs
wrote:
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.