Changing dates to date format

[Tigerxl]

I copy paste around 2 thousand dates in a website ( htlm format ) into
my XL 2003, column A.
Now the formula (=text(A2,"ddd") does not reconize the format in those dates.
How can I change those dates to date format in XL.
I tried format>cell>date>March 14,2001 and it did not work.
THANK YOU.
PS I can not sort them either.

 

[Rick Rothstein]

Can you share with those of us not familiar with web stuff an example or two
of what an "htlm format" date looks like?

 

[Tigerxl]

Sure Rick and thank for your interest.
Dates look like this : 12/31/08
12/30/08
12/29/08
Etc. on the cells and on the formula bar too.

 

[Rick Rothstein]

They look like standard dates to me, although I guess they could be Text
that looks like a date. I would think your formula should work even if those
cells are formatted as Text, so I think something else is wrong. Since you
got these dates from the web, perhaps you brought along a stray invisible
character or two. If the month portion of your dates contain a leading zero
for one-digit month numbers, then try this formula and see if it works...

=TEXT(LEFT(A1,8),"ddd")

 

[Gord Dibben]

Data>Text to columns>Next>Next>Column Data Format>Date>MDY and Finish.

Now try the formula.


Gord Dibben MS Excel MVP

 

[Tigerxl]

It sure worked.
Thank you Gord Dibben.

Data>Text to columns>Next>Next>Column Data Format>Date>MDY and Finish.

Now try the formula.


Gord Dibben MS Excel MVP