When VBA touches it, it sees it as a US style date (mm/dd/yyyy).

You will probably need to process each cell individually

Dim cell as Range, sDay as String, sMon as String, sYear as String
Dim dt as Date
For each cell in Range(cells(2,5),cells(2,5).End(xldown))
sDay = left(cell.Text,2)
sMon = Mid(cell.Text,4,2)
sYear = Mid(cell.Text,7,4)
dt = DateSerial(clng(sYear),clng(sMon),clng(sDay))
Cell.Value = dt
Cell.Numberformat = "dd.mm.yyyy"
Next

a more compact alternative would be

Dim cell as Range
Dim dt as Date
For each cell in Range(cells(2,5),cells(2,5).End(xldown))
dt = cdate(Replace(cell.Text,".","\"))
Cell.Value = dt
Cell.Numberformat = "dd.mm.yyyy"
Next

--
Regards,
Tom Ogilvy

"pamalpass" wrote in
message ...

I tend to record all my actions in a macro and then hack around to
create what i really need.

This project is to download data from a web site using the web query
and then to create a pivot table of the data.

Problem 1 occurred when trying to change text from the form 01.02.2006
to date format. I assumed this would be a recognisable date format but
i couldnt seem to make Excel understand it until i replaced the '.'
with '/'. So i used a replace all for '.' with '/' no problem. However
if i recorded this as a macro and replayed it then it made some errors.
occasionally some date would be reversed and appear as 02/01/2006.

Anyone know why it reversed the month and day?

ill post problem 2 another time
Paul


--
pamalpass
------------------------------------------------------------------------
pamalpass's Profile:
http://www.excelforum.com/member.php...o&userid=31952
View this thread: http://www.excelforum.com/showthread...hreadid=516726