Menu
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.
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.
396
To change date formats, follow the steps below -- 3 different options are show: Select cells with fixed dates, and use the quick Number formats on the Excel Ribbon. For example, click the Short Date format or Long Date format, to apply date formatting quickly.
The date format might be linked to your language preference in Excel. Check the language preference in Options>Language. If the language installed is English (United States), the date format will be American (mm/dd/yyyy). If English (United Kingdom) has been installed, the date format will be English (dd/mm/yyyy).
If you import data into Excel from another source, or if you enter dates with two-digit years into cells that were previously formatted as text, you may see a small green triangle in the upper-left corner of the cell. This error indicator tells you that the date is stored as text, as shown in this example.
Solution: You have to change to the correct value. Right-click on the cell and click Format Cells (or press CTRL+1) and make sure the cell follows the Text format. If the value already contains text, make sure it follows a correct format, for e.g. 22 June 2000.
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.
101
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")
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
