Not able to format a Date

  • Thread starter Thread starter ali
  • Start date Start date
A

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
 
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
 
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



.
Click to expand...
 
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'
 
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
Click to expand...

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
 
Back
Top