How about a UDF compromise? This gives you the VBA to use in code OR as a cell
formula as...

=FixDate2(B5)

Public Function FixDate2(rng As Range)
Dim s, m, d, y, v()
s = Left$(rng.Value, 8): ReDim v(2)
v(2) = Mid(s, 1, 4): v(1) = Right$(s, 2): v(0) = Mid(s, 5, 2)
s = Format(Join(v, "/"), "dddd dd mmmm, yyyy"): d = Split(s, " ")
Select Case Day(CDate(Join(v, "/")))
Case 1, 21, 31: d(1) = d(1) & "st"
Case 2, 22: d(1) = d(1) & "nd"
Case 3, 23: d(1) = d(1) & "rd"
Case Else: d(1) = d(1) & "th"
End Select
FixDate2 = Join(d, " ")
End Function

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion