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

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
>
>
>

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



"Toppers" wrote:

> 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
> >
> >
> >

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



"Matt" wrote:

> 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
>
>
>
> "Toppers" wrote:
>
> > 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
> > >
> > >
> > >

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