Months and Date keep being switched around when running VBA

Question

I have a column on my sheet, Column F which is full of dates. Now I need these dates to be in this format "dd,mm,yyyy" but sometimes they are in text form, us/armenian format in the original data. Why I apply a macro to convert it in the correct format, it switches the dates and months around sometimes but if I do it manually no such error occurs.

Am using paste values to get the data in my worksheet and this is my code. It shows the correct month for the format dd/mmm/yyyy but the switches the months and date.

Range("F2").Select
    Range(Selection, Selection.End(xlDown)).Select
 Selection.NumberFormat = "dd/mmm/yyyy"
    Selection.TextToColumns Destination:=Range("F2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True
 Selection.NumberFormat = "dd/mm/yyyy"

Lets take cell F2, date is 9th Jan 2009 in the format 09/01/2009.

When I do my text to columns, it leaves the date as 09/01/2009.

When I repeat the text to columns (making no changes whatsoever) it changes the date (not the format) to 1st September 2009.

Does anyone know why this is happening? If I do text to columns manually, I can repeat it all day long with it staying in the format I like.

The problem only occurs if I repeat the macro, am not sure what I am doing wrong. Have tried changing fieldinfo to array(array(1,4) to no avail.

Hope someone can help.

What do you think `Other:=False, FieldInfo _ :=Array(1, 4), TrailingMinusNumbers:=True` is doing? — Dave, Jun 09 '14 at 09:51
As an aside, I've had this issue in a macro. If I tried to write a macro with dd-mm-yyyy then every time I ran the macro, it would switch. Changing it to dd/mm/yyyy solved it ([Code showing example](http://superuser.com/questions/793137/how-to-convert-dd-mm-yyyy-date-format-to-yyyy-mm-dd-in-excel-2007/793168#793168)) — Dave, Aug 06 '14 at 11:00

score 0 · Answer 1 · answered Mar 02 '16 at 13:11

I compiled this piece of code and it did a job for me.

Sub FormatDate()

Application.ScreenUpdating = False
With Range("A2", Range("A" & Rows.Count).End(xlUp))
.EntireColumn.Insert
.NumberFormat = "@"
With .Offset(, -1)
.FormulaR1C1 = "=Text(RC[1],""dd/mm/yy"")"
.Offset(, 1).Value = .Value
.EntireColumn.Delete
End With
End With
Application.ScreenUpdating = True

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 4)

End Sub

score 0 · Answer 2 · answered Jun 18 '14 at 10:36

You need to set the datatype in your FieldInfo argument

Redim FieldInfoVal(1 To 1) 
FieldInfoVal(1) = xlDMYFormat

do don't have 4 columns so I am using an array of 1

then pass in FieldInfoVal after FieldInfo:=

You might be able to dispense with the variable by using

FieldInfo:=Array(0, xlDMYFormat)

I think the array is 1 based so it will ignore the 0th member, I have just put in a zero.

Either approach should work I think.

Months and Date keep being switched around when running VBA

2 Answers2