Date issues caused by American format on import

[kenderweasel]

Hi,

I have a VBA macro that imports data from a saved csvfile into an excel report, however, I'm having problems applying it to a newset of data because the new data has the date in American fomat, not UK.

I need to be able to export the data file with the date in American format,save it as it is (I need this to be as user-friendly as possible, so I don’twant the user to have to make any changes to the file – just save and go), andthen run a macro on the report to import any data on the export that is newerthan the existing data on the report. Ifthere is some way of doing this by keeping the date in US format (as I can setup a formula to change it on the report), then this might be the best option,otherwise, how can I make the macro convert the date before import?

MsgBox ("Select Nuance File")
'Open Nuance File
Dim OpenFileName As String
Dim wb As Workbook
'Select and Open Workbook
OpenFileName = Application.GetOpenFilename("NuanceExport.csv")
If OpenFileName = "False" Then Exit Sub
Set wb = Workbooks.Open(OpenFileName)

'Import data - "Raw Data - Nuance" - datedependent
Dim LR1 As Long
Dim LR2 As Long
Dim cl As Range
LR1 = wb.Sheets("Nuance Export").Cells(Rows.Count,"A").End(xlUp).Row
For i = 2 To LR1
Ifwb.Sheets("Nuance Export").Cells(i, "B") >WorksheetFunction.Max(ThisWorkbook.Sheets("Run").Range("$C$6"))And wb.Sheets("Nuance Export").Cells(i, "B") <=ThisWorkbook.Sheets("Run").Range("$C$3") Then
wb.Sheets("Nuance Export").Cells(i, "B").Resize(,5).Copy
LR2 =ThisWorkbook.Sheets("Raw Data - Nuance").Cells(Rows.Count,"B").End(xlUp).Row
ThisWorkbook.Sheets("Raw Data - Nuance").Cells(LR2 + 1,"B").PasteSpecial Paste:=xlPasteValues
End If
Next i

 

[Norie]

Which format do you need the dates in?

 

[kenderweasel]

I need the dates in UK format - the file I'm importing has the dates in US format.

 

[Computerman]

Kenderweasel,
Here is what I use to turn a U.S. Based Date (MM/DD/YYYY) into a UK type of date (DD.MM.YYYY). I am using periods as the separators in the UK Based date as the database system it is going into needs them to be that way

counter = 2
OrigDate = Sheet1.Cells(counter, 3).Value
Do While OrigDate <> 0
    OrigDate = Sheet1.Cells(counter, 3).Value
    If OrigDate <> 0 Then
        Sheet1.Cells(counter, 4).Value = Format(OrigDate, "dd.MM.yyyy")    this is the line that does the conversion
        counter = counter + 1
    Else
        GoTo LastLine
    End If
Loop

 

[kenderweasel]

Thanks!