Simple VBA for dates is driving me crazy

[TheRedCardinal]

My workbook has some imported data in it, currently manually pasted.

One of the columns is a date field, but the source data can vary in format and either be what should be DD.MM.YYYY or DD/MM/YYYY depending on the source spreadsheet.

As I need to work with the dates, I wrote a macro to replace the "." with "/" and then format as short date.

The incoming data should be in "General Format"

I wrote this:

        .Columns("B").NumberFormat = "General"
        .Columns("B").Replace ".", "/", xlPart, xlByRows, True
        .Columns("B").NumberFormat = "dd/mm/yyyy"

I think the first line is now redundant.

If the data has "/" separators then everything works ok.
But if it is "." then the program switches it from dd.mm to mm/dd for no reason that I fathom.

Any tips?

 

[Fluff]

Why not use Text to columns, that way you shouldn't have a problem

 

[TheRedCardinal]

Thanks Fluff.

I now have the opposite problem!

If the data has a "/" separator it now converts it into "mm/dd/yyyy"!

Do I need to put in an IF statement to distinguish between the import methods?

 

[Fluff]

As long as you select the date format the dates are in (not the format you want) it should be ok

 

[TheRedCardinal]

This must be something with the way I am using the code.

When I do it manually, it works just fine.

However when I use recorded Macro code:

        .Range(.Cells(2, 2), .Cells(LRow, 2)).TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True

I get the mix up with the data if it comes in with "/" instead of "."

I used Month() function to check and it has converted the cell "04/01/2018" into 1st April 2018 for sure.

I am guessing the macro code is wrong?

 

[Fluff]

You're quite right it does mess it up with the recorded macro, but works manually. Never had dates in differing formats, so didn't realise that.
I think your only option will be to loop through the data & convert individually.

 

[steve case]

Why not tear it apart with left & right formulas? I put it into YYYYMMDD
format because you can sort them and bypass Excel's date function which
I hate with a passion.

 

[Fluff]

Rather than looping through the range, try

Sub TheRedCardinal()
   With Range("B2", Range("B" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(isnumber(@),@,date(right(@,4),mid(@,4,2),left(@,2)))", "@", .Address))
   End With
End Sub