Converting US Dates to UK Formats
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 |
Converting US Dates to UK Formats
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 |
Converting US Dates to UK Formats
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 |
Converting US Dates to UK Formats
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 |
Converting US Dates to UK Formats
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 |
All times are GMT +1. The time now is 04:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com