Sub process_dates()
Dim myCell As Range
Dim myRng As Range
Set myRng = Range("n2:u50") ' change your range here to accomodate your needs. this can be easily made dynamic to detect cells with data
For Each myCell In myRng.Cells
strDate = myCell.Value ' adding this so you can see how it gets the value for each cell and follow the pattern
If InStr(strDate, "/") = 0 And strDate <> 0 Then ' skip if it's been run before (presense of a "/") or skip if 0
sDate = convertDate(strDate) ' pass the data to the function
myCell.Formula = sDate ' update the cell
End If
Next
End Sub
Function convertDate(ByVal strDate As String)
sYYYY = Right(strDate, 4)
Select Case Len(strDate)
Case Is < 7 ' I added this before adding IF statement in Sub, but left it here so you can see some syntax for a Select Case statement
convertDate = 0
Exit Function
Case 7
sM = "0" & Left(strDate, 1)
sD = Mid(strDate, 2, 2)
Case 8
sM = Left(strDate, 2)
sD = Mid(strDate, 3, 2)
End Select
convertDate = sM & "/" & sD & "/" & sYYYY ' (use this to have MM/DD/YYYY as a String type.
' convertDate = CDate(sM & "/" & sD & "/" & sYYYY) ' use this to convert to Date type (used for comparison)
End Function