2 Problems with a VBA Macro and Date Formating

Question

I have looked at other posts but cannot find anything that is similar enough to my problems. Any help will be appreciated. I have a set of dates that come in everyday; the dates come in the following format: DD.MM.YYYY (I live in a country that has the day first). I need the data to change into DD/MM/YYYY. I then use these dates in a Vlookup as part of the data set that holds the information I wish to retrieve. I need help with the following problems: Problem # 1

When I use the macro and switch the "." with the "/", days 1 to 12 have been switched to the following format DD/MM/YYYY. However, the actual month and day have switched. Currently working in April so 01.04.2020 has been switched to 04/01/2020 (Reading as January fourth); 04/02/2020 (February second and so on....). How can I prevent this from happening so that everything stays in place and just the "." and the "/" change. Problem #2 From day 13 and onwards the format looks right “13/04/2020”, however when I use it in the Vlookup, the formula will not bring any results back. In order for the Vlookup to work, I have to go to the cell that I just changed and press delete in front of the first digit, even though there is no space there; in order for the Vlookup to work.

Why does that happen? What can I do it so it work right after replacing the “.” and the “/”

Below is my code

Sub Dates()

Range(Range("G12"), Range("G12").End(xlDown)).Select


Selection.NumberFormat = "dd.mm.yyy"

Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

Selection.NumberFormat = "dd/mm/yyy" 

End Sub

If your regional settings use the date format dd.mm.yyyy you shouldn't need to change it to dd/mm/yyyy in Excel at all. If your data is dates, why do you first format it with a dot, then replace the dot? Why not format the dates with a / in the first step? — teylyn, Apr 22 '20 at 21:22
If SAP provides the Excel file, are the dates real dates or are they text that look like dates?? Can you format the dates in the grid with the ribbon? Does the format change? Then these are real dates and all you need to do is format them in VBA, not replacing of characters required. — teylyn, Apr 22 '20 at 21:24
Ok. I'm in Australia and based on my reigonal settings, the `dd.mm.yyyy` format isn't recognised as a date - to clarify, `01/04/2020` changed to the `Number` format stays the same whereas `01/04/2020` formatted as `Number` returns 43922 — Samuel Everson, Apr 22 '20 at 21:25
There are heaps of examples on the internet on how to solve this. Use Text to Columns and set the DMY or MDY in step 3. Or use Power Query and set the locale of the source data. — teylyn, Apr 22 '20 at 21:26
@SamuelEverson but you don't know what BetteJoan's regional settings are and if their dates are dates or not. — teylyn, Apr 22 '20 at 21:27
@teylyn perhaps I should have explicitly stated to check it. — Samuel Everson, Apr 22 '20 at 21:29
@teylyn SAP downloads like that, we need it to be DD/MM/YYY for the report to work. Can you provide me with at least one link please. — BetteJoan, Apr 22 '20 at 21:31
@SamuelEverson `dd.mm.yyyy ` is not recognized here either. Its just how SAP donwloads it. It is formatted as `General ` — BetteJoan, Apr 22 '20 at 21:33

Dy.Lee · Answer 1 · 2020-04-22T23:18:27.890

1

Try,

Sub test()
    Dim vDB, rngDB As Range
    Dim s As Variant
    Dim i As Long

    Set rngDB = Range("G12", Range("G12").End(xlDown))
    vDB = rngDB
    For i = 1 To UBound(vDB, 1)
        s = Split(vDB(i, 1), ".")
        vDB(i, 1) = DateSerial(s(2), s(1), s(0))
    Next i
    rngDB = vDB
    rngDB.NumberFormatLocal = "dd/mm/yyyy"
End Sub

Error Image

Correct Image

edited Apr 22 '20 at 23:18

answered Apr 22 '20 at 22:48

Dy.Lee

7,527
1
12
14

Samuel Everson · Answer 2 · 2020-04-22T22:59:20.133

Here is a solution for your code as is.

I strongly recommend reading and further understanding How to avoid Select in Excel VBA to improve your code and reduce risk of runtime errors.

If you add this code to the bottom of your existing procedure (as it is shown in your question) it will loop through each cell and re-format it to the correct date value.

Dim myCell As Variant
Dim myRange As Range

Set myRange = Selection

For Each myCell In myRange
    myCell.Value = Format(CDate(myCell), "DD/MM/YYYY")
Next

You might find this link helpful also:

Better way to find last used row

If you refine your code taking into account the information in both links, you will end up avoiding .Select and Selection. entirely, and your target range/cell will be less ambiguous.

I'd reformat it as follows:

Note: I have written this on Sheet1 of a new workbook, you would need to change the Sheets("...") reference to match your sheet name.

Sub dates()

Dim myRange As Range
Dim LastRow As Long
Dim myCell As Range

    With ThisWorkbook.Sheets("Sheet1")
        LastRow = .Cells(.Rows.Count, 7).End(xlUp).Row
        Set myRange = Range("G12:G" & LastRow)

        For Each myCell In myRange
            myCell.Value = Replace(myCell, ".", "/")
            myCell.Value = Format(CDate(myCell), "DD/MM/YYYY")
        Next myCell
    End With
End Sub

Now it's a lot clearer where on our workbook we are making changes which helps improve readability for yourself and others (such as other users on SO) along with reduces ambiguity and risk of RunTime errors.

score 0 · Answer 3 · answered Apr 24 '20 at 23:30

Thanks for the text to column advise this is what I did for it to work:

Sub Dates ()


Dim rg As Range

Set rg = Range("G12").CurrentRegion

    rg.TextToColumns Destination:=Range("H2"), ConsecutiveDelimiter:=True, DataType:=xlDelimited, Other:=True, OtherChar:="."

    lr = ActiveSheet.Cells(Rows.Count, "G").End(xlUp).Row

    Range("K2").Formula = "=DATE(J2,I2,H2)"

    Range("K2").AutoFill Range("K2:K" & lr)

2 Problems with a VBA Macro and Date Formating

3 Answers3

Error Image

Correct Image