Date format changes from D-M-Y to M-D-Y

Question

Out of a csv file, I obtain a variable BookingDate with value "8-9-2016". I want to loop through an Excel sheet to check, if there is a match with that value.

I discovered, that the values I read from the sheet are changed, when I want to use them in VBA. Let's say, I set a variable BookingDate2 and give it a value like this:

LR = ThisWorkbook.Sheets("Sheetname").Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    BookingDate2 = ThisWorkbook.Sheets("Boekingen AMS-IAD").Cells(i, 1).Value

Then I want to check, if it found a match like this:

If (BookingDate = BookingDate2) Then
    'Do something

BookingDate has value "8-9-2016" and the value of BookingDate2 is supposed to be "8-9-2016", but is changed to "9-8-2016".

Answer 1

I fixed it!

I forgot to Dim the variables to Date. It was like this:

Dim BookingDate As String
Dim BookingDate2 As String

But it had to be:

Dim BookingDate As Date
Dim BookingDate2 As Date

That did the trick for me.

Answer 2

If you know for certain that BookingDate2 always has the format of "d-m-y" and you want it to be "m-d-y", one approach is to split the date value, invert the order, and then save it back to the variable with the correct format before testing their values. Try this:

LR = ThisWorkbook.Sheets("Sheetname").Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    BookingDate2 = ThisWorkbook.Sheets("Boekingen AMS-IAD").Cells(i, 1).Value
    Dim aux
    aux = Split(BookingDate2,"-")
    BookingDate2 = aux(1) & "-" & aux(0) & "-" & aux(2) 'reordering the date in the desired format

    If (BookingDate = BookingDate2) Then
        'Do something

This is, of course, assuming the date formats in both your CSV and your spreadsheet does not change