Hi Ant,

The formula gets a bit (lot) munged by the NG formatting.

The end of the 1st line and start of the 2nd line should come out as:
" "
The end of the 2nd line and start of the 3rd line should come out as:
"/"
That combo correctly turns 12/25/06 into 25/12/06.

For a quick & dirty macro version of this, which will change any dates in
the selected range , try:
Sub ConvertDateFormat2()
Dim DtRange As Range
Dim oCell As Range
Dim oTxt As String
If Selection.Columns.Count * Selection.Rows.Count = 1 Then
Set DtRange = ActiveCell
Else
Set DtRange = Selection.SpecialCells(xlConstants)
End If
With Application
On Error Resume Next
For Each oCell In DtRange
oTxt = oCell.Text
oCell.Value = "'" & Mid(oTxt, .WorksheetFunction.Find("/", oTxt) +
1, _
.WorksheetFunction.Find("/", .WorksheetFunction.Replace(oTxt, _
.WorksheetFunction.Find("/", oTxt), 1, " ")) - _
.WorksheetFunction.Find("/", oTxt) - 1) & "/" & _
Left(oTxt, .WorksheetFunction.Find("/", oTxt) - 1) & "/" & _
Right(oTxt, Len(oTxt) - .WorksheetFunction.Find("/", _
.WorksheetFunction.Replace(oTxt, _
.WorksheetFunction.Find("/", oTxt), 1, " ")))
Next oCell
End With
End Sub

You'll notice the expression 'oCell.Value = "'" & Mid('. This retains the
resulting date as a string. If you want the result to be a date value
instead, change this to 'oCell.Value = Mid('. Be careful, though, as making
a mistake with this and applying it to a non-US date string may mean a lot
more work getting the date back.

Cheers

--
macropod
[MVP - Microsoft Word]


"Ant" wrote in message
...
Thanks. That kind of works, however when I used the example 12/25/06 it
returned 02/12/06 not 25/12/06. Also do you know the VBA code I could
apply a
macro button to in order to accomplish the same conversion result without
having to add your formula each time. Cheers.

"macropod" wrote:

Hi Ant,

If the data are in a text file you want to import, changing your
system's
regional settings to the US date format (eg mm-dd-yyyy) before importing
the
data should suffice. Simply change the settings back afterwards.

If you've got a worksheet that's showing numeric values in the US date
format, you can change that via Format|Cells|Number|Date.

If you've got a worksheet that's showing text strings in the US date
format,
you could use a formula like:
=MID(A1,FIND("/",A1)+1,FIND("/",REPLACE(A1,FIND("/",A1),1,"

"))-FIND("/",A1)-1)&"/"&LEFT(A1,FIND("/",A1)-1)&"/"&RIGHT(A1,LEN(A1)-FIND("/
",REPLACE(A1,FIND("/",A1),1," ")))
to turn a US date in A1 into the equivalent "dd-mm-yyyy" strings, or
=--(MID(A1,FIND("/",A1)+1,FIND("/",REPLACE(A1,FIND("/",A1),1,"

"))-FIND("/",A1)-1)&"/"&LEFT(A1,FIND("/",A1)-1)&"/"&RIGHT(A1,LEN(A1)-FIND("/
",REPLACE(A1,FIND("/",A1),1," "))))
to convert them to serial values that you could then format as dates.

Cheers

--
macropod
[MVP - Microsoft Word]


"Ant" wrote in message
...
Does anyone have some simple code that I can use to convert American
date
format to English format. eg. 12/25/06 to 25/12 06. It will have to
take
into
account single and double digits.

Cheers,
Ant.