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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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
Back
Top