我经常遇到在Excel中处理日期的问题,我一定是做错了什么,但我不明白是什么。
我有一个电子表格,是从我们的交换服务器上导出的,其中有一栏是日期。尽管我在英国,但它们都是以美国的格式出现的。
有问题的那一列看起来像这样
04/08/2012
04/09/2009
04/01/2010
04/21/2011
04/05/2012
08/30/2009
08/29/2010
08/28/2011
在Excel中,我选中了这一列,并选择了 "格式化单元格..."。在这个对话框中,我选择了 "日期",选择了 "英语(美国)"作为当地语言,并从列表中选择了匹配的日期格式。我点击 "确定",并尝试按这一列对数据进行排序。
在排序对话框中,我选择了这一列,并选择按值排序,但顺序只给了我从A到Z的选项,而不是我所期望的从最旧到最新的。
这反过来又把日期数据按前两位数排序。
我知道我可以把这个数据重新格式化为ISO,然后A到Z的排序就可以了,但我不应该这样做,我显然错过了什么。是什么呢?
编辑:我把赏金弄乱了,但这应该交给@r0berts回答,他的第一个建议是将文本转为无分隔符的列,并选择'MDY'作为数据类型。此外,如果你有一个时间(即04/21/2015 18:34:22
),你需要首先去掉时间数据。然而在这之后,@r0berts建议的方法可以正常工作。
问题是:Excel不想把日期识别为日期,即使通过"格式化单元格-数字-自定义"你明确地试图告诉它这些是日期,通过"mm/dd/yyyy
"。正如你所知道的,当excel识别了一些东西为日期时,它会进一步将其存储为数字--如"41004
",但根据你指定的格式显示为日期。为了增加混乱,excel可能只转换部分日期,如08/04/2009,而不转换其他日期,如07/28/2009。
解决方法:步骤1和2。
1)选择日期列。在数据下选择文本到列的按钮。在第一个屏幕上把单选按钮放在"分隔符"上,然后点击下一步。取消点击任何一个分隔符框(任何框空白;没有复选标记)并点击**下一步*。在列数据格式下选择日期,并在相邻的组合框中选择MDY*,然后点击完成***。现在你得到了日期值(也就是说,Excel已经将你的值识别为 "日期 "数据类型),但格式可能仍然是当地的日期,而不是你想要的 "mm/dd/yyyy"。
2) 为了正确显示所需的美国日期格式,你首先需要选择该列(如果没有选择),然后在单元格格式 - 数字下选择日期并选择地域。英语(美国)。这将给你提供像"m/d/yy
"的格式。然后你可以选择自定义,在那里你可以输入"mm/dd/yyyy
"或者从自定义字符串列表中选择这个。
替代方案:使用LibreOffice Calc。从帕特里克的文章中粘贴数据时,选择 "特殊粘贴"(Ctrl+Shift+V
)并选择 "未格式化的文本"。这将打开"导入文本" 对话框。字符集仍然是Unicode,但语言选择英语(美国);你还应该勾选"检测特殊数字"。你的日期立即以默认的美国格式出现,并且可以进行日期排序。如果你希望使用美国的特殊格式MM/DD/YYYY,你需要通过"格式化单元格"指定一次--在粘贴之前或之后。
有人可能会说--只要我通过"单元格格式"告诉它,Excel就应该识别日期,我非常同意。不幸的是,只有通过上面的第1步,我才能够使Excel识别这些文本字符串为日期。显然,如果你经常这样做,那就很麻烦了,你可以把一个可视化的基本程序放在一起,只要按一下按钮就可以完成这个任务。
数据|文本到列
更新粘贴后的一撇一捺:你可以在公式栏中看到,在不承认日期的单元格中,有一撇一捺。这意味着在格式化为数字(或日期)的单元格中有一个文本字符串。你可以说--领先的撇号阻止了电子表格对数字的识别。你需要知道在公式栏中寻找这个问题--因为电子表格只是显示一个看起来像左对齐的数字。要解决这个问题,请选择你要更正的那一列,在菜单中选择 "数据|文本到列",然后点击确定。有时你可以指定数据类型,但如果你之前已经将该列的格式设置为你的特定数据类型--你将不需要它。这个命令实际上是为了用分隔符将一个文本列分成两部分或更多,但它对这个问题也很有效果。我在Libreoffice中测试了它,但在Excel中也有相同的菜单项。