Converting US Dates to UK Formats

[Matt]

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

 

[Toppers]

Try:

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

 

[Matt]

Hi Toppers

Thanks for this
It works at first but once we get to a the 13th day of the month for formula
breaks down
Any ideas on how we can get around that?

Thanks

 

[Matt]

In case anyone is interested I have solved this problem with a combination of
Toppers and my functions

=IF(ISERROR(MONTH(A14))=TRUE,TEXT(MID(A14,4,2)&"/"&LEFT(A14,2)&"/"&RIGHT(A14,4),"dd mmm yyyy"),TEXT(MONTH(A14)&"/"&DAY(A14)&"/"&YEAR(A14),"dd mmm yyyy"))

The formula works because:
where the copied in string is a date, the day and month are switched around
where the copied in string is a just a string, the string is parsed by the
mid, left, right formulae

 

[Robert_Steel]

Matt
I know you have a solution that will work so don't want to dwell too long
on this.

However, I think the text to column tool provides a neat solution to the
problem.
If I paste some dates in mm/dd/yy format from the site you refer to.
and in the second column copy down =A1 and clear the formatting
I get the following

07/07/2006 38905
07/08/2006 38936
07/09/2006 38967
07/10/2006 38997
07/11/2006 39028
07/12/2006 39058
07/13/2006 07/13/2006

Clearly as I am in the UK my system is confused and has attempted to
register those dates it can as a dd/mm/yy format.
Where it fails I get text.

If I select the first column and do DATA\TEXT TO COLUMNS
step 1 - fixed width
step 2 - no column breaks
step 3 - Column Data Format as date MDY
Finish
I get

07/07/2006 38905
08/07/2006 38906
09/07/2006 38907
10/07/2006 38908
11/07/2006 38909
12/07/2006 38910
13/07/2006 38911

Which is what you were hoping for.

Hope this helps Rob