无法让 Excel 识别列中的日期

Question 1

问题：Excel 不想将日期识别为日期，即使您通过“设置单元格格式 - 数字 - 自定义”明确尝试通过“ mm/dd/yyyy”告诉它这些是日期。如您所知；当 excel 将某个日期识别为日期时，它会进一步将其存储为数字 - 例如“ 41004”，但根据您指定的格式显示为日期。更令人困惑的是，excel 可能只转换部分日期，例如 08/04/2009，而其他日期（例如 07/28/2009）则不转换。

解决方案：步骤 1，然后步骤 2

1）选择日期列。在数据下选择按钮文本到列。在第一个屏幕上，保留单选按钮“分隔"，然后点击下一个取消单击任何分隔符框（任何框空白的；无勾选）并点击下一个. 在列数据格式下选择日期并选择麦迪逊在相邻的组合框中，单击结束。现在您得到了日期值（即 Excel 已将您的值识别为Date数据类型），但格式可能仍然是区域设置日期，而不是mm/dd/yyyy您想要的。

2）为了正确显示所需的美国日期格式，您首先需要选择列（如果未选择），然后在单元格格式-数字选择日期并选择语言环境：英语（美国）。这将为您提供类似“ m/d/yy”的格式。然后您可以选择自定义，然后可以键入“ mm/dd/yyyy”或从自定义字符串列表中选择。

替代方案 0：使用 LibreOffice Calc。粘贴 Patrick 帖子中的数据后，选择选择性粘贴 ( Ctrl+Shift+V)，然后选择无格式文本。这将打开“导入文本”对话框。字符集仍为 Unicode，但语言选择英语（美国）；您还应选中“检测特殊数字”框。您的日期将立即以默认的美国格式显示，并可按日期排序。如果您希望使用特殊的美国格式 MM/DD/YYYY，则需要在粘贴之前或之后通过“格式化单元格”指定一次。

有人可能会说 - Excel 应该在我通过“单元格格式”告诉它日期后立即识别它，并且我完全同意。不幸的是，只有通过上面的第 1 步，我才能让 Excel 将这些文本字符串识别为日期。显然，如果您经常这样做，这会很麻烦，您可以编写一个 Visual Basic 例程，只需按一下按钮即可为您完成此操作。它可以像 Excel 中的以下 VBA 代码一样简单：

Sub RemoveApostrophe()

For Each CurrentCell In Selection
If CurrentCell.HasFormula = False Then    
CurrentCell.Formula = CurrentCell.Value
End If
Next

End Sub

替代方案 1：数据 | 文本到列

更新前导撇号粘贴后：您可以在公式栏中看到，在无法识别日期的单元格中有一个前导撇号。这意味着在格式化为数字（或日期）的单元格中有一个文本字符串，程序认为 - 您希望将其保留为文本字符串。您可以说 - 前导撇号会阻止电子表格识别数字。您需要知道在公式栏中查找此信息 - 因为电子表格只显示看起来像左对齐数字的内容。要解决此问题，请选择要更正的列，在菜单中选择Data | Text to Columns并单击“确定”。有时您将能够指定数据类型，但如果您之前已将列的格式设置为您的特定数据类型 - 则不需要它。该命令实际上旨在使用分隔符将文本列拆分为两个或更多个，但它也非常适用于这个问题。我已经在自由办公室，但有相同的菜单项Excel也。

替代方案 2：在 Libreoffice 中编辑替换

这是目前为止最快、最好的方法，但据我所知，这种方法在 MsOffice 中不起作用。Libreoffice Calc 有使用以下选项进行搜索/替换的选项正则表达式（又称正则表达式）- 您要做的就是找到单元格并用其自身替换，在此过程中，Calc 会重新将数字识别为数字并删除前导撇号。它工作得非常快。选择您的列。Ctrl-H打开查找替换对话框。选中“当前选择”和“使用正则表达式”。在查找框中输入^[0-9]- 这意味着“查找第一个位置上有数字 0 到 9 的任意单元格”. 在替换框中输入&-，对于 libreoffice 来说，这意味着“使用在搜索框中找到的字符串进行替换”。单击Replace All- 您的值将被识别为数字。妙处在于 - 它只适用于包含以撇号开头的数字的单元格，不包含其他内容 - 即它不会触及包含撇号 - 空格（或两个） - 然后是数字的单元格，或包含大写字母 O 而不是零的单元格或您想要手动更正的任何其他异常。

Answer

问题：Excel 不想将日期识别为日期，即使您通过“设置单元格格式 - 数字 - 自定义”明确尝试通过“ mm/dd/yyyy”告诉它这些是日期。如您所知；当 excel 将某个日期识别为日期时，它会进一步将其存储为数字 - 例如“ 41004”，但根据您指定的格式显示为日期。更令人困惑的是，excel 可能只转换部分日期，例如 08/04/2009，而其他日期（例如 07/28/2009）则不转换。

解决方案：步骤 1，然后步骤 2

1）选择日期列。在数据下选择按钮文本到列。在第一个屏幕上，保留单选按钮“分隔"，然后点击下一个取消单击任何分隔符框（任何框空白的；无勾选）并点击下一个. 在列数据格式下选择日期并选择麦迪逊在相邻的组合框中，单击结束。现在您得到了日期值（即 Excel 已将您的值识别为Date数据类型），但格式可能仍然是区域设置日期，而不是mm/dd/yyyy您想要的。

2）为了正确显示所需的美国日期格式，您首先需要选择列（如果未选择），然后在单元格格式-数字选择日期并选择语言环境：英语（美国）。这将为您提供类似“ m/d/yy”的格式。然后您可以选择自定义，然后可以键入“ mm/dd/yyyy”或从自定义字符串列表中选择。

替代方案 0：使用 LibreOffice Calc。粘贴 Patrick 帖子中的数据后，选择选择性粘贴 ( Ctrl+Shift+V)，然后选择无格式文本。这将打开“导入文本”对话框。字符集仍为 Unicode，但语言选择英语（美国）；您还应选中“检测特殊数字”框。您的日期将立即以默认的美国格式显示，并可按日期排序。如果您希望使用特殊的美国格式 MM/DD/YYYY，则需要在粘贴之前或之后通过“格式化单元格”指定一次。

有人可能会说 - Excel 应该在我通过“单元格格式”告诉它日期后立即识别它，并且我完全同意。不幸的是，只有通过上面的第 1 步，我才能让 Excel 将这些文本字符串识别为日期。显然，如果您经常这样做，这会很麻烦，您可以编写一个 Visual Basic 例程，只需按一下按钮即可为您完成此操作。它可以像 Excel 中的以下 VBA 代码一样简单：

Sub RemoveApostrophe()

For Each CurrentCell In Selection
If CurrentCell.HasFormula = False Then    
CurrentCell.Formula = CurrentCell.Value
End If
Next

End Sub

替代方案 1：数据 | 文本到列

更新前导撇号粘贴后：您可以在公式栏中看到，在无法识别日期的单元格中有一个前导撇号。这意味着在格式化为数字（或日期）的单元格中有一个文本字符串，程序认为 - 您希望将其保留为文本字符串。您可以说 - 前导撇号会阻止电子表格识别数字。您需要知道在公式栏中查找此信息 - 因为电子表格只显示看起来像左对齐数字的内容。要解决此问题，请选择要更正的列，在菜单中选择Data | Text to Columns并单击“确定”。有时您将能够指定数据类型，但如果您之前已将列的格式设置为您的特定数据类型 - 则不需要它。该命令实际上旨在使用分隔符将文本列拆分为两个或更多个，但它也非常适用于这个问题。我已经在自由办公室，但有相同的菜单项Excel也。

替代方案 2：在 Libreoffice 中编辑替换

这是目前为止最快、最好的方法，但据我所知，这种方法在 MsOffice 中不起作用。Libreoffice Calc 有使用以下选项进行搜索/替换的选项正则表达式（又称正则表达式）- 您要做的就是找到单元格并用其自身替换，在此过程中，Calc 会重新将数字识别为数字并删除前导撇号。它工作得非常快。选择您的列。Ctrl-H打开查找替换对话框。选中“当前选择”和“使用正则表达式”。在查找框中输入^[0-9]- 这意味着“查找第一个位置上有数字 0 到 9 的任意单元格”. 在替换框中输入&-，对于 libreoffice 来说，这意味着“使用在搜索框中找到的字符串进行替换”。单击Replace All- 您的值将被识别为数字。妙处在于 - 它只适用于包含以撇号开头的数字的单元格，不包含其他内容 - 即它不会触及包含撇号 - 空格（或两个） - 然后是数字的单元格，或包含大写字母 O 而不是零的单元格或您想要手动更正的任何其他异常。

Question 2

选择所有列并转到定位和替换，然后用"/"替换/。

Answer

选择所有列并转到定位和替换，然后用"/"替换/。

Question 3

我曾经遇到过类似的问题，从 SAP 数据库中提取了数千行数据，但令人费解的是，同一日期列中却出现了两种不同的日期格式（大多数是我们的标准格式“YYYY-MM-DD”，但约 10% 是“MM-DD-YYY”）。每月手动编辑 650 行数据是不可能的。

上述选项均无效（零... 0... nil）。是的，我全部试过了。复制到文本文件或明确导出到 txt 仍然对 10% 日期的格式（或缺乏格式）没有影响，它们只是坐在角落里拒绝表现。

我能修复它的唯一方法是在行为错误的日期列右侧插入一个空白列，并使用以下相当简单的公式：

（假设您的错误数据在Column D）

=IF(MID(D2,3,1)="-",DATEVALUE(TEXT(CONCATENATE(RIGHT(D2,4),"-",LEFT(D2,5)),"YYYY-MM-DD")),DATEVALUE(TEXT(D2,"YYYY-MM-DD")))

然后，我可以通过粘贴“值”将新计算列中的结果复制到行为错误的日期之上，之后我就可以删除计算列。

Answer