vba range.replace not setting date format properly

[Mushy peas]

I have a range (let's call it the A column) that has dates in this format: dd.mm.yyyy so I want to change the format to short dates d/m/yyyy

Instead when I use range.replace, it takes the values as if it is in the mm/dd/yyyy format. How do I replace it.

Sub format_dates()
    Sheet1.Columns(1).Replace ".", "/"
End Sub

 

[kevin9999]

Sub format_dates()
Sheet1.Columns(1).NumberFormat = "d/m/yyyy"
End Sub

 

[Mushy peas]

Sub format_dates()
Sheet1.Columns(1).NumberFormat = "d/m/yyyy"
End Sub

Sorry if I did not clarify, but my cells contains the value in this format d.m.yyyy

 

[Mushy peas]

dd.mm.yyyy I mean

 

[kevin9999]

dd.mm.yyyy I mean

Yes. I know.
Before running the code:

Book1
	A
1	14.06.2023
2	15.06.2023
3	16.06.2023
4	17.06.2023
5	18.06.2023
6	19.06.2023
7	20.06.2023
8	21.06.2023
9	22.06.2023
10	23.06.2023
Sheet1

After running the code:

Book1
	A
1	14/6/2023
2	15/6/2023
3	16/6/2023
4	17/6/2023
5	18/6/2023
6	19/6/2023
7	20/6/2023
8	21/6/2023
9	22/6/2023
10	23/6/2023
Sheet1

 

[kevin9999]

Possibly what you have are text values that look like dates and not actually dates? In a cell type =A1+1 (assuming you have a 'date' in A1). If it comes back as #VALUE! then you don't have actual dates in column A but rather text values that look like dates.

 

[Mushy peas]

Possibly what you have are text values that look like dates and not actually dates? In a cell type =A1+1 (assuming you have a 'date' in A1). If it comes back as #VALUE! then you don't have actual dates in column A but rather text values that look like dates.

Hmm somehow the values in my sheet just remains in the form dd.mm.yyyy

 

[kevin9999]

Are the 'dates' left-aligned in their cells, or do they have a ' at the left of the cell they're in? Did you try what I suggested in post #6?

 

[Mushy peas]

Ahhh I see what you mean. It is currently formatted as text so your method sadly does not work.

 

[kevin9999]

I can't get Evaluate to work properly on a single line of code, so try this (until a better solution comes along). Assumes we're looking at Sheet1 and the data starts from row 2 - adjust to suit.

Sub format_dates()
    Dim LRow As Long, LCol As Long
    LRow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
    LCol = Sheet1.Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column + 1
    
    With Sheet1.Range(Sheet1.Cells(2, LCol), Sheet1.Cells(LRow, LCol))
        .FormulaR1C1 = "=TEXT(DATEVALUE(SUBSTITUTE(RC1,""."",""/"")),""d/m/yyyy"")"
        .Value = .Value
        .Copy Sheet1.Range("A2")
    End With
    Sheet1.Columns(LCol).ClearContents
End Sub