Converting larges number of values to UK date format in Excel VBA

Question

I have a sheet with tens of thousands of dates in the following format :-

31.01.2018 (so, dd.mm.yyyy)

The cell format of each of these is General. I need to convert each of these to UK date format. To do so, I am using :-

With ThisWorkbook.Worksheets("Report")
    For i = 2 To Lastrow
        DateString = .Range("J" & i).Value
        Year = Right(DateString , 4)
        Month = Mid(DateString , 4, 2)
        Day = Left(DateString , 2)

        With .Range("J" & i)
            .Value = CDate(Day & "/" & Month & "/" & Year)
            .NumberFormat = "dd/mm/yyyy"
        End With
    Next i
End With

This takes quite a while, and I wanted to know if there was a more effective way of converting the dates?

Is there a particular reason why you don't want to find+replace `.` with `/` in the columns where your dates are? — Stavros Jon, May 08 '19 at 09:36
*"The cell format of each of these is General."* This is your actual problem. Store your dates as dates, and then format them using the cell format options. — jsheeran, May 08 '19 at 10:23

Stavros Jon · Answer 1 · 2019-05-08T10:44:11.860

0

If i understand correctly and I am not missing something, you don't need VBA to do that. Just select column J and find and replace . with /.

If you want to do it using VBA anyway, you can do this:

Sub test()
Dim sht As Worksheet
Dim rng As Range
Set sht = ThisWorkbook.Worksheets("Report")
Set rng = sht.Columns("J")
rng.Replace what:=".", replacement:="/"
End Sub

It takes less than a second to execute for around 10k dates.

EDIT:

When it's done the values will be recognized by excel as dates. The format of these dates can be set to the European one. Select column J press CTRL+1, in the Number tab, under Category choose Date and set it to European format.

Or simply add this line to he code above:

rng.NumberFormat = "dd/mm/yyyy"

edited May 08 '19 at 10:44

answered May 08 '19 at 09:51

Stavros Jon

1,695
2
7
17

If I do this, the format becomes mm/dd/yyyy. 01/02/2019 becomes 02/01/2019 for example. – bd528 May 08 '19 at 10:12
Well that has to do with the regional settings. Select column `J` press `CTRL+1`, in the `Number` tab, under `Category` choose `Date` and set it to European format. Do you need this to be done via code? – Stavros Jon May 08 '19 at 10:19
What do you mean by European format? I can't see that as an option. I tried United Kingdom, and have the same issue. Same issue with using the VBA version too. – bd528 May 08 '19 at 10:32
So after you've run the code if you select one cell and hit Ctrl+1 what number format (Category, Type, Location) does the cell have? – Stavros Jon May 08 '19 at 10:50

score 0 · Accepted Answer · answered May 08 '19 at 10:52

I was able to resolve this using the answer supplied here :-

https://stackoverflow.com/a/30609676/1936588

Changing

.TextToColumns Destination:=.Cells(1), DataType:=xlFixedWidth, FieldInfo:=Array(0, xlYMDFormat)

To

.TextToColumns Destination:=.Cells(1), DataType:=xlFixedWidth, FieldInfo:=Array(0, xlDMYFormat)

Converting larges number of values to UK date format in Excel VBA

2 Answers2