Excel奇怪的行为with dates =>文本 [英] Excel weird behaviour with dates => text

问题描述

我有一吨的细胞含有日期如22/12/2013。这将自动设置为日期格式。如果我尝试将单元格格式更改为文本（简单地说，因为我希望它是文本而不是一个日期，以便稍后可以用PHP读取它），事情完全改变。

以下是复制错误的方法。

1. 写入单元格22/10/2013


2. 将格式更改为TEXT


3. 原始内容更改为41569

我需要一种方法来解决这个问题，因为否则当我用PHP读取日期时，它会达到41569。但是，如果我设法使其成为文本，这将是正常的。

解决方案

正如andy holaday所说，使用单元格格式来更改格式实际上不会将现有的数字（日期）更改为文本。您可以使用文本到列功能来执行此操作：

选择列的日期>数据>文本到列>下一步>下一步>在步骤3的列数据格式选择文本>完成

转换现有日期，如果您有输入要输入的日期，可以添加撇号，如Polly所述，或者您可以简单地将条目列格式化为文本格式。

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.

解决方案

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.

这篇关于Excel奇怪的行为with dates =>文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！