Change date format.

larsgunnar007 

Please give the following code a try which may or may not work as dates are tricky to handle.

My default date format is mm/dd/yyyy and changed it to mm.dd.yyyy to test the code for you and it worked on my end but not sure how this would work on your end.

Insert a New Module say Module2 and place the codes given below on to Module2 and run the code ConvertToDates. The code will prompt you to select the Date cells and you should select the green cells with dates and press OK to continue.

After code is run, let me know if the code converts all the dates into the real dates.

Dim dtFound As Boolean

Sub ConvertToDates()
Dim Rng As Range
Dim Cel As Range

On Error Resume Next
Set Rng = Application.InputBox("Please select the Cells with Date in them.", "Convert Date String Into Real Date!", Type:=8)
On Error GoTo 0

Application.ScreenUpdating = False

If Rng Is Nothing Then
    MsgBox "You didn't select the Date Cells!", vbExclamation
    Exit Sub
End If

For Each Cel In Rng
    Cel.Value = DateStringToDate(Cel.Text)
    If dtFound Then
        Cel.TextToColumns Destination:=Cel
        dtFound = False
    End If
Next Cel
Rng.NumberFormat = "dd.mm.yyyy"
Application.ScreenUpdating = True
End Sub

Function DateStringToDate(ByVal str As String)
Dim Matches As Object
Dim y       As Long
Dim m       As Long
Dim d       As Long
With CreateObject("VBScript.RegExp")
    .Global = False
    .Pattern = "(\d{4})\.(\d{2})\.(\d{2})"
    If .Test(str) Then
        dtFound = True
        Set Matches = .Execute(str)
        y = Matches(0).SubMatches(0)
        m = Matches(0).SubMatches(1)
        d = Matches(0).SubMatches(2)
        DateStringToDate = DateSerial(y, m, d)
    Else
        DateStringToDate = str
    End If
End With
End Function

In the attached, I have placed the code on Module2 for you to test.