Geek Answers Handbook

Excel weird behavior with dates => text

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

Here's how to replicate the error.

  • Writing to cell 10/22/2013
  • Change the format to TEXT
  • Source content changed to 41569

I need a way to fix this, because otherwise, when I read the date with PHP, it still goes to 41569. But if I manage to make the text, everything will be fine.

+6
source share
3 answers

As andy holaday says, using cell formatting to change the format does not actually change existing numbers (dates) to text. You can do this with text-to-column functionality:

Select the date column> Data> Text to Columns> Next> Next> in step 3 under "Column Data Format" select "text"> "Finish"

This converts existing dates, if you have input dates that you want to enter as text, you can add an apostrophe, as Polly says, or you can simply pre-format the input column as a text format.

+4
source

Enter dates with an apostrophe at the beginning. '22/10/2013 will be interpreted as a text string.

+2
source

It's not a mistake. Changing the format of a cell does not change the value that is stored in this cell. Excel stores dates as numeric values. I think you want to change the numerical value to a date-like text. Try this formula in a new cell somewhere:

= TEXT(A1,"dd/mm/yyyy")
+2
source

More articles:


All Articles