Cleans up date’s entered as string values in a given range
Author: michael@excelexperts.co.uk
Sub CleanDates_V1(Rng As Range)
DateValue : converts a text string representing a date into a date, errors if not a date
Cdate : converts a text string representing a date into a date, errors if not a date
IsDate : evaluates if a string can be interpreted as a date, or if a variable is in a date format
DateSerial : converts a date into a date serial by using integers for year, month, day
Dim c As Range
Dim sDateFormatx As String
sDateFormatx = "dd/mm/yyyy"
Rng.NumberFormat = "0"
For Each c In Rng
With c
On Error Resume Next
If Not IsEmpty(.Value) Then
Test for a string date, and convert if found
If Application.WorksheetFunction.IsText(.Value) = True Then
If Not IsError(DateValue(.Value)) Then
.Value = DateValue(.Value)
.NumberFormat = sDateFormatx
End If
End If
Test for a numeric type date
If Not IsDate(Format(.Value, sDateFormatx)) Then
'Debug.Print "Unrecognisable date: " & c.Address
End If
End If
On Error GoTo 0
End With
Next
End Sub