Date Format Issue

[VbaHell]

I really need help on this issue please
I am using Excel 2003 and trying to complete a VLookup between a CSV File and a Excel File which brings back a list of dates but as you can see the dates come out mixed in the same column, some are american and some english, I need then all to be english format, I have spent many hours trying to solve this so any help would be great. Below is the code I used.

<TABLE style="WIDTH: 92pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=122 border=0 x:str><COLGROUP><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4461" width=122><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 92pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=122 height=17 x:num="40583">09-02-11</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="40583">09-02-11</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>25/07/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>13/07/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>13/07/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="40826">10-10-11</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="40826">10-10-11</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>15/08/2011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>15/08/2011</TD></TR></TBODY></TABLE>

Workbooks.Open Filename:="C:\tmp\B3XOS.CSV"
Columns("D:D").Select
Selection.NumberFormat = "dd-mm-yy"
Range("D1").Select
Windows("Energy CVI v1.xls").Activate
Range("Q6:Q1500").Select
Selection.Clear
Selection.NumberFormat = "dd-mm-yy"
Range("Q6").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-12],B3XOS.CSV!C2:C4,3,FALSE)"
Selection.Copy
Range("Q6:Q1500").Select
ActiveSheet.Paste
Columns("Q:Q").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("Q5").Select
Application.CutCopyMode = False

End Sub

 

[VoG]

Try selecting the dates, Data > Text to Columns, click Next twice, tick Date, select DMY then click Finish.

 

[VbaHell]

Hi VOG

I have tried doing this on both sheets and I still have the problem

In the CSV file the date format is 02/09/2011 but when it's dropped into Excel it changes to 09/02/11

 

[p45cal]

Things like this usually go wrong on the first line:
Workbooks.Open Filename:="C:\tmp\B3XOS.CSV"
because helpful Excel will already have tried to interpret dates, regardless of format etc.
So instead of just opening the file, record a macro of yourself importing the data onto a new sheet.
In Excel, use Data|Import External Data|Import Data, then under files of type at the bottom of the dialogue, choose Text files, select your file and run through the text import wizard. In step 3 of 3, you'll be able to set the date column to import as a date formatted as you wish.
Later you can delete the new sheet.

 

[Marcelo Branco]

Try

Text to Columns > Next > Next and change Date to MDY
Finish

M.

 

[VbaHell]

P45cal you are correct that it is the first line where the problems starts, I will try your suggestion and let you know how I get on. Thank you for the help