CODEWITHSUNDEEP
excelvba
Jessie
Jessie

Reputation: 3

Macro find and replace to change string to date

I used the macro to find and replacement function to change the string to date. It worked well with some of the dates but went wrong also. The code I used is:

Worksheets(“Sheet 1”).Columns(“H”).Replace What:=“.”,
Replacement:=“/“,
SearchOrder:=xlByColumns,
MatchCase:=True

The original string is "11.01.2019" and the date should be 11/01/2019 which is 11 January 2019.

But when I used the macro, it was converted to 01/11/2019, which is 1 November 2019.

The code worked well if the day is bigger than 12.

For example "13.01.2019" will be converted to 13/01/2019 correctly.

Anyone can help with the code to make it work properly?

Many thanks.

Upvotes: 0

Views: 72

Answers (3)

Axel Richter
Axel Richter

Reputation: 61975

This happens when Windows regional settings tells Excel that English date format D/M/Y shall be used.

Then the default date delimiter is / as in en_US too but the sequence of the date parts differs from en_US (M/D/Y). When VBA runs, it always runs in en_US locale settings and so it gets 11/01/2019 to be November 1. 2019 in VBAalready. But 13/01/2019 is not a date in en_US. So VBA does not converting it into a date. The Excel then converts the string "13/01/2019" into the correct date according to Windows regional settigs.

Workaround:

Do using Range.TextToColumns method instead.

Worksheets("Sheet 1").Columns("H:H").TextToColumns Destination:=Range("H1"), DataType:=xlDelimited, FieldInfo:=Array(1, xlDMYFormat)

You needs avoiding calling the TextToColumns more than one times. Because then VBA will reconverting the D/M/Y dates into en_US M/D/Y again if possible.

Upvotes: 1

jsheeran
jsheeran

Reputation: 3037

The following will construct a date in yyyy-mm-dd format, which should be accepted regardless of your locality, and populate the cell accordingly (but formatted according to your system settings).

Dim r As Range, cell As Range
Set r = [H2:H999] ' or wherever your data is
For Each cell In r
  cell = Right(cell, 4) & "-" & Right(Left(cell, 5), 2) & "-" & Left(cell, 2)
Next cell

Upvotes: 0

Tim Stack
Tim Stack

Reputation: 3248

I recall that VBA automatically assumes American date notation. Try:

Worksheets(“Sheet 1”).Columns(8).NumberFormat = "dd-mm-yyy"

Worksheets(“Sheet 1”).Columns(“H”).Replace What:=“.”, Replacement:=“/“, SearchOrder:=xlByColumns, MatchCase:=True

Upvotes: 0

Related Questions