excel更改日期格式无效_Excel日期不会更改格式

excel更改日期格式无效

Have you ever imported data into Excel, from your credit card statement, or somewhere else, and found that Excel dates won't change format? And, if you try to sort that column of dates, things end up in the wrong order.

您是否曾经从信用卡对帐单或其他地方将数据导入Excel，发现Excel日期不会更改格式？ 而且，如果您尝试对那列日期进行排序，那么事情将以错误的顺序结束。

That happened to me this week, and here’s how I fixed the problem, using a built-in Excel tool.

这周发生在我身上，这就是我使用内置的Excel工具解决问题的方式。

日期为文字 (Dates As Text)

In the screen shot below, you can see the column of imported dates, which show the date and time. I didn’t want the times showing, but when I tried to format the column as Short Date, nothing happened – the dates stayed the same.

在下面的屏幕快照中，您可以看到导入日期的列，其中显示了日期和时间。 我不希望显示时间，但是当我尝试将列格式设置为“短日期”时，什么也没发生–日期保持不变。

Why won’t the dates change format? Even though they look like dates, Excel sees them as text, and Excel can’t apply number formatting to text.

为什么日期不更改格式？ 即使它们看起来像日期，Excel也会将它们视为文本，并且Excel无法将数字格式应用于文本。

There are a few signs that the cell contents are being treated as text:

有一些迹象表明单元格内容被视为文本：

• The dates are left-aligned
  日期左对齐
• There is an apostrophe at the start of the date (visible in the formula bar)
  日期开头有撇号(在编辑栏中可见)
• If two or more dates are selected, the Quick Calc in the Status Bar only shows Count, not Numerical Count or Sum.
  如果选择了两个或多个日期，则状态栏中的“快速计算”仅显示计数，而不显示数值计数或总和。

确定日期 (Fix the Dates)

If you want to sort the dates, or change their format, you’ll have to convert them to numbers – that’s how Excel stores valid dates. Sometimes, you can fix the dates by copying a blank cell, then selecting the date cells, and using Paste Special > Add to change them to real dates.

如果要对日期进行排序或更改其格式，则必须将其转换为数字，这就是Excel存储有效日期的方式。 有时，您可以通过以下方式修复日期：复制空白单元格，然后选择日期单元格，然后使用“选择性粘贴”>“添加”将其更改为实际日期。

Unfortunately, that technique didn’t work on this data, probably because of the extra spaces. You could go to each cell, and remove the apostrophe, but that could take quite a while, if you have more than a few dates to fix.

不幸的是，可能由于多余的空间，该技术无法在此数据上使用。 您可以转到每个单元格并删除撇号，但是，如果要修复的日期不止几个，则可能要花费相当长的时间。

A much quicker way is to use the Text to Columns feature, and let Excel do the work for you:

一种更快的方法是使用“文本到列”功能，然后让Excel为您完成工作：

• Select the cells that contain the dates
  选择包含日期的单元格
• On the Excel Ribbon, click the Data tab
  在Excel功能区上，单击“数据”选项卡
• Click Text to Columns
  单击文本到列

In Step 1, select Delimited, and click Next

在步骤1中，选择“定界”，然后单击“下一步”。

• In Step 2, select Space as the delimiter, and the preview pane should show the dates divided into columns.
  在第2步中，选择“空格”作为定界符，然后预览窗格应显示分为几列的日期。
• Click Next
  点击下一步

In Step 3, you can set the data type for each column:

在步骤3中，您可以为每一列设置数据类型：

• In the preview pane, click on the date column, and select Date
  在预览窗格中，单击日期列，然后选择日期
• In the Date drop down, choose the date format that your dates are currently displayed in. In this example, the dates show month/day/year, so I’ve selected MDY.
  在“日期”下拉列表中，选择当前显示日期的日期格式。在此示例中，日期显示为月/日/年，因此我选择了MDY。

• Select each of the remaining columns, and set it as “Do not import column (skip)”
  选择其余的每个列，并将其设置为“不导入列(跳过)”

• Click Finish, to convert the text dates to real dates.
  单击完成，将文本日期转换为实际日期。

格式化日期 (Format the Dates)

Now that the dates have been converted to real dates (stored as numbers), you can format them with the Number Format commands.

现在，日期已转换为实际日期(存储为数字)，您可以使用数字格式命令将其格式化。

There are a few signs that the cell contents are now being recognized as real dates (numbers):

有迹象表明，单元格内容现在已被识别为实际日期(数字)：

• The dates are right-aligned
  日期右对齐
• There is no apostrophe at the start of the date (visible in the formula bar)
  日期开头没有撇号(在编辑栏中可见)
• If two or more dates are selected, the Quick Calc in the Status Bar shows Count, Numerical Count and Sum.
  如果选择了两个或多个日期，则状态栏中的“快速计算”将显示“计数”，“数字计数”和“总和”。

To format the dates, select them, and use the quick Number formats on the Excel Ribbon, or click the dialog launcher, to see more formats.

要设置日期格式，请选择日期，然后使用Excel功能区上的快速数字格式，或单击对话框启动器以查看更多格式。

Everything should work correctly, after you have converted the text dates to real dates.

将文本日期转换为实际日期后，一切都应正常工作。

下载样本文件 (Download the Sample File)

To follow along with this tutorial, get the Date Format Fix Sample file from my Contextures website, on the Excel Dates Fix Format page.

要继续本教程，请从Contextures网站上的Excel Dates Fix Format页面上获取Date Format Fix Sample文件。

翻译自: https://contexturesblog.com/archives/2014/01/30/excel-dates-wont-change-format/

excel更改日期格式无效