Hi,

I need to write a macro that will change the following: -

09/27/2004 to 27/09/2004

In other words from US format to UK format.

I have a number of columns that need to be converted at the same time.

Any help would be appreciated

Thanks

Mat

--
Emy
-----------------------------------------------------------------------
Emyn's Profile: http://www.excelforum.com/member.php...fo&userid=1614
View this thread: http://www.excelforum.com/showthread.php?threadid=47019

the following code will do:

Code:
--------------------

For Each cell In [your desired range] 'e.g. Range("A1:Z500")
cell.Value = Mid(cell.Value, 4, 2) & "/" & Mid(cell.Value, 1, 2) & "/" & Right(cell.Value, 4)
Next cell
--------------------


add a button to execute it and replace "[your desired range]"


Regards

Simon

---------
by the way, if you want to change a whole column it's easier to write
Columns("A:A") instead of Range("A1:A65536")


--
moondark
------------------------------------------------------------------------
moondark's Profile: http://www.excelforum.com/member.php...o&userid=27390
View this thread: http://www.excelforum.com/showthread...hreadid=470195

If these are true date entries they will appear in the proper format
automatically given the user's regional settings under Windows Control
Panel. So if you have US regional settings and enter the date 9/27/2005,
this workbook will display the date as 27/09/2005 automatically when opened
on a machine with UK regional settings.

--
Jim
"Emyn" wrote in message
...
|
| Hi,
|
| I need to write a macro that will change the following: -
|
| 09/27/2004 to 27/09/2004
|
| In other words from US format to UK format.
|
| I have a number of columns that need to be converted at the same time.
|
| Any help would be appreciated
|
| Thanks
|
| Matt
|
|
| --
| Emyn
| ------------------------------------------------------------------------
| Emyn's Profile:
http://www.excelforum.com/member.php...o&userid=16148
| View this thread: http://www.excelforum.com/showthread...hreadid=470195
|

Thanks for the reply guys.

Regards

Matt


--
Emyn
------------------------------------------------------------------------
Emyn's Profile: http://www.excelforum.com/member.php...o&userid=16148
View this thread: http://www.excelforum.com/showthread...hreadid=470195

Hi,

The PC we are using was configured to UK settings. Therefore when
opening the spreadsheet with US dates anything that could be both a US
date and a UK date, was translated incorrectly to UK format (ie
06/07/2004 became 6th July 2004 rather than 7th June 2004).
Any dates that were not recognised as possible UK format dates were
formated as Text and therefore could not be converted back to UK (ie a
date of 07/24/2004 was automatically converted to Text and we do not
have 24 months in the UK callender.

The solution was to convert the PC's settings to US prior to opening
the document so that when the document was opened dates were recognised
as US format. As this point we saved the document and then reset the
PC's setting to UK and reopened the document. When doing this the
document was converted to UK format.

I hope this help anyonw with the same problem.

Regards

Matt


--
Emyn
------------------------------------------------------------------------
Emyn's Profile: http://www.excelforum.com/member.php...o&userid=16148
View this thread: http://www.excelforum.com/showthread...hreadid=470195

Obviously this file is not an actual workbook or the troublesome data in the
workbook is not stored as Dates - thus excel is forced to attempt a
conversion when the workbook is opened. So, for a workbook with dates
stored as dates, you machinations are not required.

--
Regards,
Tom Ogilvy

"Emyn" wrote in message
...

Hi,

The PC we are using was configured to UK settings. Therefore when
opening the spreadsheet with US dates anything that could be both a US
date and a UK date, was translated incorrectly to UK format (ie
06/07/2004 became 6th July 2004 rather than 7th June 2004).
Any dates that were not recognised as possible UK format dates were
formated as Text and therefore could not be converted back to UK (ie a
date of 07/24/2004 was automatically converted to Text and we do not
have 24 months in the UK callender.

The solution was to convert the PC's settings to US prior to opening
the document so that when the document was opened dates were recognised
as US format. As this point we saved the document and then reset the
PC's setting to UK and reopened the document. When doing this the
document was converted to UK format.

I hope this help anyonw with the same problem.

Regards

Matt


--
Emyn
------------------------------------------------------------------------
Emyn's Profile:
http://www.excelforum.com/member.php...o&userid=16148
View this thread: http://www.excelforum.com/showthread...hreadid=470195