Excel Date Formatting changes after using Find/Replace

Question

I have a bunch of dates in a volume which are in UK format but separated by dots not forward slashes.

Every time I run a macro to replace dots with dashes and use the line of code below, excel always seems to think that the months and day are the other way round

Replace What:=".", Replacement:="/"
NumberFormat = "dd/mm/yyyy"

As an example: 03.07.18 Will become 07/03/2018 despite formatting as UK.

How can I keep the date as 03/07/2018

Do you have *dates* or *strings* ? – FunThomas Jun 27 '18 at 12:07 — FunThomas, Jun 27 '18 at 12:07
See https://stackoverflow.com/q/20375233/78522 – iDevlop Jun 27 '18 at 12:23 — iDevlop, Jun 27 '18 at 12:23

Darren Bartrup-Cook · Accepted Answer · 2018-06-27T12:39:22.773

3

You could try splitting into a DateSerial:

Sub Test()

    Dim r As Range
    Set r = Sheet1.Range("B10")

    r = DateSerial(Split(r, ".")(2), Split(r, ".")(1), Split(r, ".")(0))

    r.NumberFormat = "dd/mm/yyyy"

End Sub

Split returns a zero-based, one-dimensional array containing a specified number of substrings..

What this means in plain English is that it takes your text string of 03.07.18and splits it into three parts using the ..
So you end up with 03, 07 and 18 within an array.

It might be easier to read as:

v = Split(r, ".")
r = DateSerial(v(2), v(1), v(0))

DateSerial takes three numbers (year, month and day) and returns a date. The three parts of the Split are passed into this to create the date.

To process on a number of cells use:

Sub formatfixer()
    Dim r As Range
    For Each r In Range("H5:H120")
        r = DateSerial(Split(r, ".")(2), Split(r, ".")(1), Split(r, ".")(0))
        r.NumberFormat = "dd/mm/yyyy"
    Next r
End Sub

edited Jun 27 '18 at 12:39

answered Jun 27 '18 at 12:20

Darren Bartrup-Cook

18,362
1
23
45

This works! I have no idea what it means but thanks! – Bing Jun 27 '18 at 12:29
This seems like a stupid question, but I want to repeat this for Range H5:H120. I changed your code above from (“B10”) to (“H5:H120”) and it doesn’t work? Ideas? – Bing Jun 27 '18 at 12:33
That worked a treat and thank you for expanding on your answer. Top Man – Bing Jun 27 '18 at 20:10

score 1 · Answer 2 · answered Jun 27 '18 at 12:12

Easier to change the " . " in the format rather than in the value:

the code:

Sub formatfixer()
    Dim r As Range, formt As String
    For Each r In Intersect(Selection, ActiveSheet.UsedRange)
        formt = r.NumberFormat
        If InStr(1, formt, ".") > 0 Then
            r.NumberFormat = "dd/mm/yyyy"
        End If
    Next r
End Sub

the result:

score 0 · Answer 3 · answered Jun 27 '18 at 12:08

0

After the replace, you can try to clear the date format and apply the NumberFormat method:

.Range(yourRange).ClearFormats
.Range(yourRange).NumberFormat = "dd/mm/yyyy"

answered Jun 27 '18 at 12:08

JC Guidicelli

1,296
7
16

Excel Date Formatting changes after using Find/Replace

3 Answers3