Excel VBA - Convert Text Date format into date format

Hi Jens,
Thanks a lot for your reply.
I have tried your code but it shows error msg, saying type mismatching while i could not find out where was wrong.

Also can i bother you to have a look my code below? it runs a bit slow and i have accelerated it by using both
Application.ScreenUpdating = False, and
Application.CutCopyMode = False

Don't know if you could help and make it faster.
Many thanks
Bin

Option Explicit

Sub HotelComplaint()

Application.ScreenUpdating = False
Application.CutCopyMode = False
Application.DisplayAlerts = False

'Text to column the raw data
ActiveSheet.Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(3, 1), Array(8, 1), Array(29, 1), Array(34, 1), _
Array(43, 1), Array(58, 1), Array(65, 1), Array(69, 1), Array(79, 1), Array(85, 1), Array( _
94, 1), Array(109, 1)), TrailingMinusNumbers:=True
Range("A1").Select

'remove the top 10 rows
Rows("1:10").Delete

'insert a column in column A and fill with data "x"
Range("a1").EntireColumn.Insert
ActiveCell.Range("A1:A10000").Value = "x"

'go to a2 and use do loop to remove all rows that start with " ","Cy","__" in column B. _
Do until the row starts with "To"
Range("a2").Select

Do Until ActiveCell.Offset(0, 1) = "To"

If ActiveCell.Offset(0, 1) = "" Or _
ActiveCell.Offset(0, 4) = "" Or _
ActiveCell.Offset(0, 1) = "Cy" Or _
ActiveCell.Offset(0, 1) = "__" Then
'remove any row starts with ""
'remove any row starts with figures
'remove any row srates with "Cy"
'remove any row starts with "__"
ActiveCell.EntireRow.Delete
ElseIf ActiveCell.Offset(1, 0).Select Then
End If
Loop

'Remove the inserted column with "x"
Range("a1").EntireColumn.Delete

'Rename column header
Range("a1").Select
ActiveCell.Value = "CountryCode"
ActiveCell.Offset(0, 1).Value = "Supp._City"
ActiveCell.Offset(0, 1).Select
ActiveCell.Offset(0, 1).Value = "Supplier"
ActiveCell.Offset(0, 1).Select
ActiveCell.Offset(0, 1).Value = "Svc._City"
ActiveCell.Offset(0, 1).Select
ActiveCell.Offset(0, 1).Value = "Arrival_Date"
ActiveCell.Offset(0, 1).Select
ActiveCell.Offset(0, 1).Value = "Tour_Ref._Site/Bkg"
ActiveCell.Offset(0, 1).Select
ActiveCell.Offset(0, 1).Value = "Agent"
ActiveCell.Offset(0, 1).Select
ActiveCell.Offset(0, 1).Value = "Nat"
ActiveCell.Offset(0, 1).Select
ActiveCell.Offset(0, 1).Value = "Comp_Type"
ActiveCell.Offset(0, 1).Select
ActiveCell.Offset(0, 1).Value = "Resp."
ActiveCell.Offset(0, 1).Select
ActiveCell.Offset(0, 1).Value = "Reason"
ActiveCell.Offset(0, 1).Select
ActiveCell.Offset(0, 1).Value = "Profit/Loss_ST"
ActiveCell.Offset(0, 1).Select

'Remove the last 3 rows
Dim LastRows As Range
Set LastRows = Cells(Rows.Count, "A").End(xlUp)
LastRows.Resize(3).EntireRow.Delete

'Remove Remark Column
Dim rng As Range
With Worksheets("RawData").Range("A1:z1")
Set rng = Worksheets("RawData").Range("A1:z1") _
.Find(WHAT:="Remarks", _
LookAt:=xlWhole, MatchCase:=False)
Do While Not rng Is Nothing
rng.EntireColumn.Delete
Set rng = .FindNext
Loop

End With

'reset the font and font size on both TotalData and RawData tabs
Cells(1).CurrentRegion.Select
Selection.Font.Name = "Arial"
Selection.Font.Size = 10

Range("A1:L1").Font.Bold = True

'reset date format
Dim i As Variant
For Each i In ActiveSheet.Range("e2:e10000")
i.Value = DateValue(i.Text)
Next
Selection.NumberFormat = "dd/mm/yyyy"

ActiveSheet.Columns.AutoFit

'copy data over from RowData to TotalData & autofit the columns

Sheets("TotalData").Select

If Sheets("totaldata").Range("a1") = "" Then
Sheets("RawData").Select
Cells.CurrentRegion.Copy

Sheets("TotalData").Select
ActiveSheet.Range("a1").PasteSpecial

ElseIf Cells(1) <> "" Then
Sheets("RawData").Select
Range("a2", Range("a2").End(xlToRight).End(xlDown)).Copy

Sheets("TotalData").Select
ActiveSheet.Range("A1").End(xlDown).Offset(1, 0).Select
ActiveCell.PasteSpecial

End If

ActiveSheet.Columns.AutoFit

'Refresh the pivot table
Sheets("Pivot").Select
Dim pivotTable As pivotTable
For Each pivotTable In ActiveSheet.PivotTables
pivotTable.RefreshTable
Next

Sheets("RawData").Cells.Delete

'Show a msg box indicates done of the task
MsgBox "Done"


End Sub