converting date format to text format

**membership** · 03-17-2010, 01:36 AM

I am trying to convert a cell with date format to text, but it can't be done. Can anyone help?

**sandeep.warrier** · 03-17-2010, 01:44 AM

Hi

If A1 contains the date, then try

=TEXT(A1,"dd/mm/yyyy")

or any other format you prefer.

**membership** · 03-17-2010, 02:01 AM

I've tried, it works. But i hv a problem which i copy a cell (A1) from another sheet, it reads 19970107, to a new cell, the A1 (19970107) turns into 2E+07. I think probably there is a hidden formula or something in this A1 cell. Could you help?

**DonkeyOte** · 03-17-2010, 03:09 AM

You don't stipulate the format that the number represents - ie yyyymmdd or yyyyddmm - we'll presume the former.

=TEXT(TEXT(A1,"0000-00-00"),"dd/mm/yyyy")