CODEWITHSUNDEEP
excelexcel-2007
Tarariraz
Tarariraz

Reputation: 113

Excel weird behaviour with dates => text

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.

Upvotes: 7

Views: 22405

Answers (3)

barry houdini
barry houdini

Reputation: 46371

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.

Upvotes: 7

andy holaday
andy holaday

Reputation: 2302

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

Upvotes: 3

Polly Shaw
Polly Shaw

Reputation: 3237

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

Upvotes: 3

Related Questions