下载掌阅APP，畅读海量书库
立即打开
日期是一类特殊的数据，也是非常容易出错的数据。例如，把日期输入成“2021.7.20”“20210720”的格式，而这种输入格式完全违背了Excel对日期的处理规则。
从本质上来说，日期是从1开始的正整数序号。例如，1表示1900年1月1日；2表示1900年1月2日；2021年7月20日对应数字44397。
Excel处理的最小日期是1900-1-1，最大日期是9999-12-31。
一般情况下，修改错误日期最高效的方法是使用分列工具。在某些特殊情况下，需要使用函数来解决。
使用分列工具是最简单、最快速的方法，可以瞬间修改某列中所有的非法日期，只保留正确的日期。
图2-78是从管理系统导出的发货单，A列的日期是文本型日期，并不是合法日期，现在需要将其转换为真正的日期（数值型日期），详细的操作步骤如下。
图2-78 A列中的文本型日期
步骤1 选中A列，选择“数据”→“数据工具”→“分列”命令，打开“文本分列向导”对话框，第1步和第2步保持默认，在第3步中选中“日期”单选按钮，如图2-79所示。
图2-79 选中“日期”单选按钮
步骤2 单击“完成”按钮，就得到了正确的日期，如图2-80所示。
图2-80 文本分列得到正确的日期
尽管使用分列工具修改非法日期是非常简便的，但需要注意：在分列向导的第3步中要选择设置正确的日期组合格式。
如图2-81所示的非法日期，除J列外，其他各列的非法日期都可以按照前面介绍的方法快速修改。
图2-81 非法日期示例
修改J列的日期需要在文本分列向导的第3步中进行设置，在“日期”单选按钮后的下拉列表中按照单元格日期组合的格式（年、月、日三个数字的组合顺序）选择相应选项，这里在下拉列表中选择MDY，因为单元格的日期数字组合顺序是“月－日－年”，如图2-82所示。
图2-82 选择日期组合MDY
此外，分列工具只能用在一列数据中，当有几列非法日期需要修改时，需要分别选择这些列，一列一列地应用分列工具进行转换。
在某些情况下无法使用分列工具修改非法日期，此时需要借助函数。具体使用哪些函数，需要根据具体情况进行选择。
如图2-83所示的示例数据表，将员工的进公司时间分成年、月、日三列数字保存，如果要获得一个完整的进公司时间，必须使用DATE函数将表示年、月、日的三个数字组合成正确的日期，公式如下：
=DATE(F3,G3,H3)
DATE函数很简单，就是把表示年、月、日的三个数字组合成日期。
下面的公式是错误的。
=F3&"-"&G3&"-"&H3
图2-83 使用DATE函数生成正确的日期
在制作数据自动化分析模板时，可能需要直接利用系统导出的原始数据进行计算，而在导出的原始数据中日期是非法日期时，就不能使用分列工具进行转换，此时也必须使用函数公式来处理。
如图2-84所示的示例数据表，左侧A～C列是从系统导出的原始数据，A列日期是非法的（210501表示2021年5月1日），现在要求利用这三列原始数据制作右侧的统计报表，这里月份名称是月份的英文简称，如Jan、Feb等。
图2-84 左侧是原始数据，右侧是要求的统计报表
既然要求直接使用原始数据制作统计报表，就只能使用函数来处理了。首先将A列的日期处理为正确的日期格式，然后将日期转换为月份的英文简称。G4单元格的计算公式如下：
=SUMPRODUCT((TEXT(TEXT($A$2:$A$15,"00-00-00"),"mmm")=$F4)*1,
($B$2:$B$15=G$3)*1,
$C$2:$C$15)
在这个公式中，表达式 TEXT($A$2:$A$15,"00-00-00")表示使用TEXT函数将非法日期转换为正确的日期格式（尽管这个日期是文本格式，但可以使用函数直接计算），计算结果如图2-85所示。
图2-85 使用公式直接转换日期
在表格中输入当天的日期可以直接按 Ctrl+;（分号）组合键。
如果要在某列输入一个以天为单位的日期序列，只需在第一个单元格中输入开始日期，然后往下拖动填充柄即可。
如果要在某列输入每月1日的日期，则在第一个单元格中输入如2021-1-1，然后往下拖动填充柄，再单击最后一个单元格右下角的“自动填充选项”按钮
，展开一个自动填充选项列表，从中选中“以月填充”单选按钮即可，如图2-86和图2-87所示。
图2-86 利用自动填充选项快速输入日期序列
图2-87 得到的每月1日的日期序列
日期是正整数，因此可以设置自定义格式，将表格中的日期显示为各种需要的样式，让表格更加容易阅读。
设置日期格式是在“设置单元格格式”对话框中进行的，除了在“分类”列表框中套用一些日期的固定格式外，还可以自定义日期格式，即选择“自定义”选项，然后在“类型”文本框中输入格式代码，如图2-88所示，就可以得到指定的格式。
图2-88 设置日期的自定义格式
在设置日期的自定义格式时，按表2-1中的日期格式代码任意组合，可以得到需要的效果。
表2-1 日期格式代码
例如，可以使用自定义日期格式的方法设计一个动态表头的考勤表，如图2-89所示，详细制作过程请观看本节视频。
图2-89 动态表头的考勤表