Yes, you can use a formula, but that means having both sets of 'dates' in the workbook - and using a range to recalculate the dates.
The macro converts the dates in-situ without the need for that extra range. If you add it to your personal.xls workbook, the macro
will be available for any worksheet you might need it for, without having to add the macro to that worksheet's workbook.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

"FARAZ QURESHI" wrote in message ...
Cant there be a simple formula using functions such as FIND, FORMAT, LEFT,
RIGHT?

"macropod" wrote:

Hi Faraz,

The following macro converts date strings and values in the selected range between mm/dd/yyyy and dd/mm/yyyy. The output is in
the
form of date values that you can format as dates via Format|Cells|Number|Date.

Sub ConvertDateFormat()
Dim DtRange As Range
Dim oCell As Range
Dim oTxt As String
If Selection.Cells.Count = 1 Then
Set DtRange = ActiveCell
Else
Set DtRange = Selection
End If
With Application
On Error Resume Next ' In case there are no xlConstants or convertible dates in DtRange
For Each oCell In DtRange.SpecialCells(xlConstants)
oTxt = "'" & oCell.Text
oCell.Value = Mid(oTxt, 2, .WorksheetFunction.Find("/", oTxt) - 2) & "/" & Mid(oTxt, _
.WorksheetFunction.Find("/", oTxt) + 1, .WorksheetFunction.Find("/", _
.WorksheetFunction.Replace(oTxt, .WorksheetFunction.Find("/", oTxt), 1, " ")) - _
.WorksheetFunction.Find("/", oTxt) - 1) & "/" & Right(oTxt, Len(oTxt) - .WorksheetFunction.Find("/", _
.WorksheetFunction.Replace(oTxt, .WorksheetFunction.Find("/", oTxt), 1, " ")))
Next oCell
End With
End Sub

Note that no conversion is performed if the date is embedded in a formula or the output would be an invalid date.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

"FARAZ QURESHI" wrote in message
...
Upon exporting a data from another and importing therefrom from in excel I
have come across the following problem:

The date format in the source data was m/d/yyyy, whereas I have the data
format in destination file to be formatted as d/m/yyyy.

Source data like 2/13/2000 has been converted to string as 2/13/2000 as
there is no 13th month, but 11/2/2000 has been converted to 11/2/2000
(correct date format but INCORRECT VALUE of 11-Feb-2000, actual one being
2-Nov-2000).

How to get the correct dates in format of d/m/yyyy.

Thanx in advance!

FARAZ