Date Format

[babypink2807]

I have copied and pasted a word table into excel which was a pain in
itself. The date format in word was Thu 21st June 2007. But the
table would not sort into date order so I had to remove the "Thu"
part. I then had to remove the paragraph sign as it was throwing
extra cells in excel when I pasted it. Eventually the date went into
excel but now I cannot format the date.

When I look at the format 21st June 2007 it has defaulted to General
under "format cell". When I change the format to say custome date it
does not make a difference, the only way it shacges is if I manually
type the date in. Is there a quicker way?

 

[Guest]

Say you have a column of text values that should be dates, for example A1:

Thu 21st June 2007

First enter the following UDF:

Function date_it(r As Range) As Date
Dim v As String
v = r.Value
s = Split(v, " ")
n = Len(s(1)) - 2
v = Left(s(1), n) & " " & s(2) & " " & s(3)
date_it = DateValue(v)
End Function

Next format another cell as Date and enter:
=date_it(A1) to display:
6/21/2007

This will be a "real" date.