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

hagea_dan

New Member
Joined
Aug 14, 2014
Messages
19
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.
Code:
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?
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Gary's Student

Well-known Member
Joined
Aug 4, 2012
Messages
1,015

ADVERTISEMENT

See if this meets your needs:
Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 14, 2014
Messages
19

ADVERTISEMENT

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.

Code:
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

New Member
Joined
Aug 14, 2014
Messages
19
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

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,659
Try this code (Note: lightly tested):

Code:
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
 
Last edited:

hagea_dan

New Member
Joined
Aug 14, 2014
Messages
19
Try this code (Note: lightly tested):

Code:
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.
 

Forum statistics

Threads
1,136,447
Messages
5,675,903
Members
419,591
Latest member
mersanko

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top