Try:

=TEXT(MONTH(A1)&"/"& DAY(A1)&"/"&YEAR(A1)," dd mmm yyyy")

"Matt" wrote:

Hello Everyone

I have copied down a currency table, from
http://www.oanda.com/convert/fxhistory
The dates are in the US format

09/04/2005
09/05/2005
09/06/2005

Normally this would not be a problem as I would just adjust the cell
formatting and they would be fine. However in this case when I paste the
dates in they convert automatically to UK format. If I link to the cells in
the next column over my data looks like this

09/05/2005 09 May 2005
09/06/2005 09 Jun 2005
09/07/2005 09 Jul 2005
09/08/2005 09 Aug 2005
09/09/2005 09 Sep 2005
09/10/2005 09 Oct 2005

Normally I use the function like
=TEXT(MID(A13,4,2)&"/"&LEFT(A13,2)&"/"&RIGHT(A13,4),"dd mmm yy")
To solve the problem
But in this case it returns something like 51/38/8451
As the actual value of the cell is 38451

I'm afraid I'm stuck now

Thanks for reading this far and I hope you can help