Hi Kiwis,

Try something like:

'=============
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim iRow As Long

Set WB = Workbooks("Book1")
Set SH = WB.Sheets("Sheet2")

With SH
iRow = .Range("B" & Rows.Count).End(xlUp)
Set Rng = .Range("B1:B" & iRow)
End With

Rng.NumberFormat = "mm/dd/yy"

End Sub
'<<=============


---
Regards,
Norman


"kiwis" wrote in message
oups.com...
Hi

I need help in converting date which is in dd/mm/yyyy to mm/dd/yyyy
format.

I need to write a macro that will convert column B which holds the
date to
the correct format mm/dd/yyyy & write back to same column B but in new
format.

sample data
orginal data converted data
B B(mm/dd/yyyy)
19/12/2002 12/19/2002
15/12/2001 12/15/2001

Thank you for providing any ideas.

rgds
kiwis