如何修改Excel中的非法日期-英雄云拓展知识分享

    我们在使用Excel的时候，有很多人对日期数据的输入是很随意的，有时输入的“日期”其实不是真实的日期格式。而从数据库导入的数据中，很多情况下的日期也其实不是真实的日期，而是文本。因此，这种情况下，不论甚么时候拿到甚么样的表格。首先要检查表格中的日期是不是为真实的日期。今天我们就来学习下如何修改Excel中的非法日期。

    在Excel中，日期是作为数值存储在单元格中的。因此，这种情况下，虽然显示格式多种多样，它们实际上都是数值。

    Excel将日期存储为序列号（称为序列值）。默许情况下，1900年1月1日是序列号1.依此类推。2表示1900年1月2日。3表示1900年1月3日…一40 278就是2010年4月10日。在处理日期数据时。记住一点就能够了：日期数字永久是正整数。

    因此，这种情况下，假设在单元格中输入了一个数字40 278.将其转换为日期。就表示2010年4月10日。

    Excel所能处理的日期最小值是1900年1月0号（对应数字0）。日期最大值是9999年12月31号（对应数字2 958—465）。0⑵ 958 465之间的数字都可以转换为台法的日期和时间。如果输入的数字超越了这个区间。就不能转换为合法日期。而是非法的日期了。

    例如在单元格中输入20100410，虽然它是一个数字。但超过了Excel所能处理的最大日期数字2 958 465。

    了解了Excel处理日期的基本规则。在输入日期时就需要依照正确的格式输入。输入日期的正确格式是年、月、日3个数字之间用减号（-）或斜杠（/）隔开。倒如。正确的日期输入格式为2010⑷⑴0、2010t/4/10、10/Apt/2010或10-Apt⑵010。但是。如果输入2010.04.10或20100410就大错特错了。

    用户可以依照习惯采取一种简单的方法输入日期。倒如。要输入日期2010⑷⑴0.下面的任何一种方法都是可行的：

    键入“2010⑷⑴0”；

    ·键入“2010/4/10”：

    ·键入“2010年4月10日”：

    ·键入“4⑴0”；

    ·键入“4/10”：

    ·键入“4月10日”：

    ·键入。10⑷⑴0”：

    ·键入“10/4/10”；

    ·键入“10-Apr⑴0”：

    ·键入“10一Apr一2010”；

    ·键入“10一Apr”；

    ·键入“Apt一10”。

    另外，Excel接受采取两位数宇输入的年份。但会进行不同的处理。

    ·00—29：Excel将00—29之间两位数字的年解释为2000⑵029年。例如。输入日期

    “19⑸⑵8.Excel假定该日期为2019年5月28日。

    ·30—99：Excel将30—99之间两位数字的年解释为1930⑴999年。例如。输入日期

    “98⑸⑵8”。Excel将假定该日期为1998年5月28日。

    如果工作表中存在有大量的非法日期。就必须先将这些非法日期修改成真实的日期。针对实际情况。可以采取不同的方法。下面举例进行说明。

    在很多情况下。某列的日期数据都是非法日期。或大部份是非法日期。这主要产生在从数据库导入数据的场合。此时。一10一个地修改单元格是不现实的。可使用。分列。工具批量修改非法日期。

    图1所示为一些非法日期的例子。将这些非法日期转换为真正日期的量简便方法。是利用“分列”工具，下面介绍其具体方法和步骤。

图1

    1、选择B列数据。注意每次只能选择一列数据。

    2、单击“数据”选项卡。再单击“数据工具”功能组中的“分列”按钮，如图2所示。

图2

    此时会打开“文本分列向导-步骤之1（共3步）”话框，如图3所示。

图3

    3、单击两次“下一步”按钮，打开“文本分列向导-步骤之3共3步”对话框，在“列散据格式”选项组当选择“日期”单选按钮，在右边的下拉列表框当选择日期格式“YMD”，如图4所示。

图4

    4、单击“完成”按钮，便可将B列的非法日期转换为真实的日期。

    其他列非法日期的转换也是采取这类方法依样画葫芦。最后的结果如图5所示。

图5

    需要注意的是，在转换J列的非法日期时，在“文本分列向导-步骤之3（共3步）对话框中，要在日期格式下拉列表框中要选择“MDY”格式，由于原始数据的格式是“月-日-年”。

    有些人在设计合同管理表格时。会把合同的起始日期和截止日期放在一起。如图6所示。这类表格结构虽然看起来比较紧凑。数据表示得也比较清楚。但是非常不利于数据分析。倒如。没法把某年某月签订合同或续签合同要到期的员工挑选出来。也没法设计一个台同到期提早提示功能。

图6

    规范的表格应当是把合同的起始日期和截止日期分成两列保存。而不是图6所示的那样保存在一列中。如果已做成了这样的表格。怎样将表格整理成规范的表格呢？

    仔细视察表格数据。可以看出日期数据是比较标准和有规则的。都是诸如“2006-09-01”这样的数据。它有10个宇符。这样就能够使用LEFT函数和RIGHT函数把合同的起始日期和截止日期分别取出来。具体步骤以下：

    1、重斯设计表格，在第一行下面插入一行，在E列右边插入两列，在H列右边插入两列，输入标题，并合并有关的单元榕，如图7所示。

图7

    2、在单元格F3中输入公式“=DATEVALUE（LEFT（E3，10））”，并向下复制，得到第一次合同的开始日期。在单元格C3中输入公式“=DATEVALUE（RIGHT（E3，10））”，并向下复制，得到第一次台同的截止日期。

    3、在单元格I3中输入公式“=IF（H3=""，""，DATEVALUE（LEFT（H3，10）））”，并向下复制，得到续签合同的开始日期。

    在单元格J 3中输入公式“=IF（H3=""，""，DATEVALUE（RIGHT（H3，10）））”，并向下复制得到续签合同的截止日期。结果如图8所示。

图8

    注意：利用文本函数LEFT和RIGHT得到的日期数据是文本日期，因此在公式中还必须使用DATEVALUE函数将这个文本日期转换为真实的日期。

    4、采取选择性粘贴的方法，将单元格中的公式转换为数值，方法是：选择全部数据区域，按【Ctrl+C】组合键复制，再右击要粘贴的位置，在弹出的快捷菜单当选择“选择性粘贴”命令，打开“选择性粘贴”对话框。选择“数值”单选按钮，如图9所示。单击“肯定”按钮。

图9

    5、删除原始的两列合同日期。便可得到规范的合同管理表格，如图1 0所示。 

图10

   虽然我们学会了如何修改Excel中的非法日期，但是我们最好还是在建立表格的时候就创建正确的日期格式，这样我们就不用在后面进行修复日期了，这样对我们做数据分析也有很大的好处。