3.6 应用实例
本节通过分析几个在Excel日期数据处理过程中经常会遇到的问题，来对Excel日期和时间型数据的处理做进一步的说明。
3.6.1 包含日期格式的连接
在Excel的数据处理中，经常需要将日期和时间型数据与其他类型的数据进行连接。如果直接将日期和时间型数据与其他类型的数据使用字符串连接符号“&”进行连接，则Excel会将日期和时间型数据转换为其序列值后再与其他类型的数据连接。所以，为了能够使日期和时间型数据显示在连接后的字符串内，需要先将日期和时间型数据使用TEXT函数处理为文本型，再与其他类型的数据进行连接。
如图3-37所示，单元格C2中是直接使用公式“=A2&B2”连接后得到的结果，得到的是一个数值，该数值是由“2018-4-7”的序列值“43197”与收入“12123354654.88”连接后得到的。
图3-37 日期连接
在单元格C3中使用函数TEXT对连接的数据进行处理，以得到指定格式的结果，使用的公式为：
=TEXT(A3, "yyyy年mm月dd日")&"的收入是：USD"&TEXT(B3, "#, ##0.00")
式中，公式“TEXT(A3, "yyyy年mm月dd日")”让单元格A3内的日期以指定的“yyyy年mm月dd日”格式显示；公式“TEXT(B3, "#, ##0.00")”让单元格B3以指定的“#, ##0.00”格式显示。
一般公式中的“0”“#”“? ”都表示占位符。
格式“0”是强制占位符，如果数字的位数少于格式中零的数量，则显示非有效零。例如，公式“=TEXT(1.2, "00.00")”的计算结果为“01.20”。
格式“#”也是占位符，但仅对有效数字有效，数字位数小于格式中规定的位数时，不会自动补零。例如，公式“=TEXT(1.256, "##.##")”的计算结果为“1.23”，公式“=TEXT(1.2, "##.##")”的计算结果为“1.2”。
格式“? ”是空格占位符，如果数字的位数少于格式中零的数量，则用一个空格占位，一般用于让数据对齐。例如，公式“=TEXT(1.256, "? ? .? ? ")”的计算结果为“□1.26”，在数值前面有一个空格；公式“=TEXT(1.2, "##.##")”的计算结果为“□1.2□”，在数值前后各有一个空格。
在实际使用中，通常将占位符组合使用。例如，通过组合使用占位符，可以让一列中的数据以小数点对齐的形式显示。
3.6.2 文本型转换为日期型
在收集的数据中，可能会遇到使用英文表示月份的情况，如图3-38中A列所示。在实际处理中，这些英文月份是无法参与运算的，需要将它们转换为数值型参与后续的运算。当然，可以使用VLOOKUP函数，将对应的日期进行转换，但是需要进行建立对应关系等操作，略显复杂。这里，可以先将A列数据转换为一个日期型数据，然后使用MONTH函数从中提取出月份。在Excel中，日期型数据可以使用“英文月份-数字”这样的形式显示，例如“Jan-1”表示1月1日。针对“Jan-1”可以使用MONTH函数从中提取出它的月份值。
图3-38 月份转换
本例思路是：首先，将A列数据加上“-数字”，让其转换为日期型数据；其次，使用MONTH函数从日期型数据中提取出月份。
MONTH函数用来提取其参数内的月份，例如“=MONTH("2018-4-7")”的值是“4”。
在B列构造辅助列，在单元格B2内使用公式“=A2&"-1"”，将A列数据全部转换为日期和时间型数据，如图3-38中B列所示。
在C列计算月份，在单元格C2中使用公式“=MONTH(B2)”，用来提取单元格B2中日期的月份。
对于时间数据的处理，可以采用相同的方法来完成。例如，图3-39中A列的数据是文本型数据，无法按照时间型数据从中提取出分钟的数值。要想提取出分钟数，可以先让这些数据转换为时间型数据。为了方便处理，也可以让其转换为以时间形式显示的文本型数据。例如，在A列中，当前的数据仅有分钟数和秒数，在其前面加上小时数，即可将数据处理为时间形（并不是时间型，是以时间型显示的文本型数据），进而提取出其中的分钟数。
图3-39 时间转换
在B列构造辅助列，用于将A列数据转换为完整的包含“小时”“分钟数”“秒”的时间数据。在单元格B2中，输入的公式为：
="12时"&A2
经过上述处理后，在B列得到可以使用公式处理的时间数据。
在C列计算分钟数。在单元格C2中，使用的公式为：
=MINUTE(B2)
3.6.3 提取日期和时间
在Excel中，可能会遇到如图3-40中单元格A2所示的完整的日期和时间型数据。在实际计算过程中，我们可能仅仅需要该数据的日期部分或者时间部分。如果仅需要显示日期或时间，则直接设置单元格的格式即可。但如果需要让日期或时间参与后续的计算，就需要使用公式将要用到的日期或时间从整体中提取出来，否则就可能会产生计算错误。
图3-40 时间差
例如，在图3-40的单元格C2中使用公式“=B2-A2”的计算结果大约为“0.4999”。而通常情况下，我们希望得到的结果是1。针对这种情况，就需要将日期从单元格A2和B2中提取出来计算。
在Excel中，日期是一个整数序列值，时间是一个小数序列值。所以，将单元格A2中的整数部分提取出来就得到了日期值，将其中的小数部分提取出来就得到了时间值。例如，在图3-41中，通过取整和取小数的形式将日期和时间分别从单元格A2中提取出来。
图3-41 提取日期和时间
在单元格B2中使用的公式为“=INT(A2)”；在单元格C2中使用的公式为“=A2-INT(A2)”。
针对图3-40，可以处理为如图3-42所示。
图3-42 处理结果
在单元格A3中，使用公式“=INT(A2)”可以提取出单元格A2的整数值“2018-8-8”。可以看到，整数值对应的时间是“0:00”。
在单元格B3中，使用公式“=INT(B2)”可以提取出单元格B2的整数值“2018-8-9”。
在单元格C3中，使用公式“=B3-A3”，得到两个日期相减的结果“1”。
3.6.4 重设日期系统
1900年不是闰年，当年不存在“2月29日”，但在Excel的日期和时间数据中这一天是存在的。
例如，在图3-43所示的单元格A2内输入“1900年2月28日”，拖动其填充柄填充单元格区域A3:A11，在单元格A3内显示了日历中并不存在的“1900年2月29日”。
图3-43 错误示例
这是微软当年为了让Excel与当时处于市场主导地位的软件兼容，在系统内故意保留的一个错误。为了避免这一错误，微软还提供了“1904日期系统”，让时间序列是一个从“1904年1月1日”开始的正整数序列。
在“Excel选项”对话框的“高级”选项中，可以对“使用1904日期系统”进行设置，如图3-44所示。
图3-44 “Excel选项”对话框
当发现Excel中的时间数据存在问题时，可以查看该选项，看是否有必要对其进行调整。