Date Format

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

babypink2807@googlemail.com - 28 Sep 2007 07:25 GMT

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?

Reply to this Message

Gary''s Student - 28 Sep 2007 15:56 GMT

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.

Signature

Gary''s Student - gsnu2007

> 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
[quoted text clipped - 7 lines]
> does not make a difference, the only way it shacges is if I manually
> type the date in. Is there a quicker way?

Reply to this Message