Change column format to date (day / month / year) with VBA

[hagea_dan]

Hello,

I encounter a minor problem which drives me crazy because I can't find the solution to it.
Basically I have a big table and one of the columns contain european style dates (day / month / year). What I want to do is change the date format through VBA so that the full name of the month will be displayed.
For example: 10/12/2014 becomes 10/December/2014
I tried the following but it doesnt work.

Range("A:A").NumberFormat = "dd/MMMM/yyyy"

After running the above line the date 10/12/2014 becomes 12/October/2014 instead of the desired result 10/December/2014.
Why does excel consider the first number (10) as the month if I specified the format dd/MMMM/yyyy. Clearly I haven't understand how dates format work.
Can someone please give me a solution?

 

[Sektor]

What locale do you use?

 

[hagea_dan]

What locale do you use?

Do you mean local time? I use UTC +2:00.

 

[Sektor]

No, Windows Regional Settings.

 

[Gary's Student]

See if this meets your needs:

Sub DateFixer()
    Dim r As Range, rng As Range
    Set rng = Intersect(Range("A:A"), ActiveSheet.UsedRange)
    For Each r In rng
        v = r.Text
        If v <> "" And InStr(1, v, "/") <> 0 Then
            ary = Split(v, "/")
            r.Value = DateSerial(ary(2), ary(0), ary(1))
            r.NumberFormat = "dd/MMMM/yyyy"
        End If
    Next r
End Sub

 

[Norie]

Are they 'real' dates or 'text' dates?

You can check that using ISNUMBER which will return FALSE if they are 'text' dates.

If that is the case you can try this to convert them to 'real' dates.

1 Select column with dates.

2 Goto Data>Text to columns and on the 3rd step choose DMY from the Column data format dropdown.

3 Click Finish.

You should now have 'real' dates which you can format however you like.

 

[hagea_dan]

No, Windows Regional Settings.

It was on USA and now I changed the format and location to my own country but now I encounter another problem.
The column I want to format contains both date and time. It looks like this: 02.11.2016 04:55
First I remove the time from all cells and then I want to change the date format in dd/MMMM/yyyy but now the date doesn't change at all.

Columns("A:A").replace What:=" *", Replacement:="", LookAt:=xlPart    <--- with this line I remove the time from the cells
Columns("A:A").NumberFormat = "dd/MMMM/yyyy"   <--- This doesn't do anything

Here is a sample of cell values if you want to test your code on them:

02.11.2016 04:55
08.11.2016 10:25
07.11.2016 08:24

It doesn't matter if the date contains . or /
I just need to remove the time, which I am able to do, and then change the date format.

 

[hagea_dan]

Are they 'real' dates or 'text' dates?

You can check that using ISNUMBER which will return FALSE if they are 'text' dates.

If that is the case you can try this to convert them to 'real' dates.

1 Select column with dates.

2 Goto Data>Text to columns and on the 3rd step choose DMY from the Column data format dropdown.

3 Click Finish.

You should now have 'real' dates which you can format however you like.

After doing these steps, the line that changes the date format now works. Thank you.
Can this be done only with VBA?

Columns("A:A").NumberFormat = "dd/MMMM/yyyy" Works only after applying the above steps.

 

[Tetra201]

Try this code (Note: lightly tested):

Sub Test()
    Columns("A:A").Replace What:=" *", Replacement:="", LookAt:=xlPart
    Columns("A:A").TextToColumns Destination:=Range("A1"), _
        DataType:=xlDelimited, FieldInfo:=Array(1, xlDMYFormat)
    Columns("A:A").NumberFormat = "dd/MMMM/yyyy"
End Sub

 

[hagea_dan]

Try this code (Note: lightly tested):

Sub Test()
    Columns("A:A").Replace What:=" *", Replacement:="", LookAt:=xlPart
    Columns("A:A").TextToColumns Destination:=Range("A1"), _
        DataType:=xlDelimited, FieldInfo:=Array(1, xlDMYFormat)
    Columns("A:A").NumberFormat = "dd/MMMM/yyyy"
End Sub

It works! Thank you all for your support.