[SOLVED] Recorded macro to find and replace date separator

Recorded macro to find and replace date separator

I have a column with dates in dd-mm-yyyy format.

I am trying to replace "-" with "." so all dates selected look like 01.01.2025 instead of 01-01-2025.

It works when I use ctrl+H in an Excel sheet but recorded VBA code does not work.

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

Solution

In Excel, dates are stored as serial numbers. For example, January 1, 2025, is stored as 44601. The format you see on your screen (dd-mm-yyyy) is just a display format.

Your macro is trying to replace "-" with "." in what it sees as numbers, not text. Since there are no "-" characters in the serial numbers, the replacement doesn't happen.

Instead of replacing characters, you can change the date format directly using cell formatting:

Sub FormatDates()
    Dim rng As Range
    Set rng = Selection
    
    rng.NumberFormat = "dd.mm.yyyy"
End Sub