Not able to format a Date

[ali]

I'm coping a table from the web into excel. I can't get
excel to recognize the date as a date. For example, the
date is June 8, 2004 and I want it to recognize this as a
date and be able to format it as a number or 8/8/04, but
when I click on Format, Cell, Number and the format I
want, nothing happens. How do I fix this? Thanks, Ali

 

[Frank Kabel]

Hi
reason for this Excel has stored this value as Text. You may try the
following:
- first format the cells with the custom format 'MMMM D,YYYY'
- now select an empty cell and copy this cell
- select the cells with the date values
- goto 'Edit - Paste Special' and choose 'Add'
- now try changing the format again

 

[ali]

This does not work.

-----Original Message-----
Hi
reason for this Excel has stored this value as Text. You may try the
following:
- first format the cells with the custom format 'MMMM D,YYYY'
- now select an empty cell and copy this cell
- select the cells with the date values
- goto 'Edit - Paste Special' and choose 'Add'
- now try changing the format again

--
Regards
Frank Kabel
Frankfurt, Germany



.

 

[Frank Kabel]

Hi
what exactly does happen?. Also you may describe how you originally
have entered these date values (are they imported)?
Another idea would be to use the following formula in a helper column
=DATEVALUE(MID(A1,FIND(" ",A1)+1,FIND(",",A1)-FIND(" ",A1)-1) & "-" &
LEFT(A1,FIND(" ",A1)-1) & "-" & RIGHT(A1,4))
and copy this down for all rows. Afterwards select this column, copy it
and goto 'Edit - Paste Special' and choose 'Values'

 

[Ron Rosenfeld]

I'm coping a table from the web into excel. I can't get
excel to recognize the date as a date. For example, the
date is June 8, 2004 and I want it to recognize this as a
date and be able to format it as a number or 8/8/04, but
when I click on Format, Cell, Number and the format I
want, nothing happens. How do I fix this? Thanks, Ali

Web stuff frequently has CHAR(160) appended.

Try this formula to convert the text string to a date:

=--TRIM(SUBSTITUTE(A1,CHAR(160),""))

You should get a 5 digit date serial number which you can then format as a
recognizable date.


--ron