Excel weird behaviour with dates => text

Question

I have a ton of cells that contain dates such as 22/12/2013. This is set automatically as Date format. And if I try to change the cell format to text (Simply because I want it to be text rather than a date so that I can read it later with PHP), the thing changes completely.

Here's how to replicate the error.

1. Write in a cell 22/10/2013
2. Change the format to TEXT
3. The original content gets changed to 41569

I need a way to fix this, because otherwise when I read the date with PHP, it gets convereted to 41569 nonetheless. But if I manage to make it text, it will be alright.

Answer 1

As andy holaday says, using cell formatting to change the format won't actually change existing numbers (dates) to text. You can do that with "text to columns" functionality:

Select column of dates > Data > Text to columns > Next > Next > at step 3, under "column data format" choose "text" > Finish

That converts existing dates, if you have dates to enter which you want to enter as text you can add an apostrophe, as Polly says, or you can simply pre-format the entry column as text format.

Answer 2

It's not an error. Changing the format of a cell does not change the value that is stored in that cell. Excel stores dates as numerical values. What I think you want to do is change the numerical value into text that resembles a date. Try this formula in a new cell somewhere:

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