date数据类型的正确格式_说说数据类型 上篇日期

EXCEL内置了多种数据类型，我们今天简单介绍一下日期这个类型。

一、日期类型简介

EXCEL中，日期这一数据类型只能表示公元1900年1月1日开始到公元9999年12月31日为止的日期。不在此范围内的日期，例如1898年的某一天，只能以文本这一数据类型表示，而不能以日期这一数据类型表示。不能表示有两重意思：

1. EXCEL不能对范围外的日期进行日期类型特有的操作和计算。

   比如，我们用可以对两个日期类型轧差求其中过去的天数；

   也可以对某个日期类型使用weekday()函数求它是周几。

   但是这样的操作，无法适用于早于1900年1月1日和晚于9999年12月31日的日期。

2. 如果将“1898-1-5”这样的日期的格式设置为长、短日期，会报错。

设置日期类型通常可以使用如下方法：

1. 按照一定标准直接输入EXCEL可识别的日期。

   例如：在国内，可以直接以“YYYY-MM-DD”或“YYYY年MM月DD日”的格式录入支持范围内的日期，EXCEL可以自动将录入的内容设置为日期格式。

2. 对已经存在的文本类型或数值类型，可以将其格式设置为长/短日期。

   如果它在支持范围内，EXCEL直接转换；如果不在支持范围内，EXCEL返回错误。

根本上来说，在EXCEL中，每个日期类型被表示为距离1900年1月0日的天数的方式。对于一个数值型整数1，如果我们将它的类型设置为日期，就会得到1900年1月1日。如下图所示：对于32，应当是1900年的第32天，显然是2月1日。

图01 数值转换成日期：

这一处理方式带来这样的功能：

1. 对数值轧差可以很简单的求解两个日期之间的天数。

2. 可以用小数表示时间。

   例如1.5表示1天及12小时。

这些就是日期的基本内容。

二、日期类型的简单操作和函数

EXCEL为日期这一数据类型提供了多种操作和函数。我们这里介绍比较常用的一些。

1. 计算两个日期之间的天数：

   如果我们用一个日期减去另一个日期，可以得到两者之间相隔的天数。

   例如：

   使用”1990-1-2”-“1990-1-1”，得到的结果是1，也就是两者之间相隔1天。

   在实际应用的时候我们要注意如果要计算两个日期之间如果算头算尾共有多少天，就要对轧差的结果加1，否则结果是错误的。

   例如：

   想要知道2000年共计多少天，可以”2000-12-31”-“2000-1-1”+1，也可以”2001-1-1”-“2000-1-1”。

2. year(), month(), day()：

   这三个函数从一个日期类型中提取其年、月、日的数值。

   注意：

   这几个函数也可以接受一个数值，EXCEL会首先试图将数值转换为日期类型并进行计算；

   如果数值超出日期类型支持的范围会返回#NUM!错误。

3. date(year, month, day)：

   接受年、月、日的数值，返回一个对应的日期类型。

4. today()：

   返回今天的日期，注意它只能返回系统设置的“今日”，不保证结果真的是现实中的今日。

   相似的，now()函数按系统设置返回当前日期和时间，注意：

   它返回录入公式后的日期和时间后保持不变，直到a.对工作表进行重新计算或b.重新打开工作簿时自动刷新。

三、几个具体应用

下面我们看几个对于日期的具体应用

1. 使用vlookup函数查找日期对应的数据

既然日期是EXCEL支持的数据类型，vlookup函数就可以直接查找某个日期对应的数据。这里，我们介绍一个特殊的用法：通过将vlookup函数的第四个参数设置为true来进行查找。

我们看这样一个问题：某家企业维护了一张人员统计表，记录了每个时点的人员总数。每当有人员变动(入职、离职、退休等情况)，该企业负责人员就将当天的人数记录到台账中，例如：

图02 vlookup函数查找日期 数据：

现在的问题是：如何查找任意一天的人数？例如2000年1月1日开始到2000年5月2日结束的这几天，该企业一直有50人；2000年5月3日开始直到2000年7月4日结束的这段时间，该企业一直有53人。这样的问题可以通过将vlookup函数的第四个参数设置为True，即进行模糊查找的方式处理。

vlookup函数的模糊查找，根据EXCEL的帮助文档，功能如下：

如果为TRUE或省略，则返回精确匹配值或近似匹配值。也就是说，如果找不到精确匹配值，则返回小于lookup_value(也就是第一个参数，作者注)的最大数值。table_array(也就是第二个参数，作者注)第一列中的值必须以升序排列；否则VLOOKUP可能无法返回正确的值。

A列数据恰好是升序排列的那么，如果我们想要查找2000年11月4日的员工人数，我们这样设置公式：

=vlookup(date(2000,11,4),$A:$B,2,true)

即可。vlookup函数将查找到不大于2000年11月4日的最大值，也就是2000年7月5日，并返回其对应的第二列的值。

这个公式，有时可以很方便的从一个按照日期排序的参数表中查找到适用的参数。例如，中国人民银行会公布历年来基准利率的数值，但其中往往只有基准利率调整当天的日期和基准利率。通过vlookup函数的设置，我们可以简单地提取任意一天的基准利率。唯一要注意的是：因为我们要查找一个日期，vlookup函数的第一个参数或者引用一个存放了日期类型的单元格；要么用date函数生成一个日期类型。

2. 日期组成部分之间的转换

这个问题源于作者本人的工作经历：对于有起止日的一条数据，我们希望按照“3个月、6个月、1年、2年、3年、5年”的模式来分类，以用于下一步计算。

简单地对起止日轧差计算天数差，会因为闰年的问题难以准确处理(当然忽略小数取证的话也是可以的)。后来我的解决思路是“化归转化”，我们求间隔的月数，对月数按“3、6、12、24、36、60”来分类。注意到1年=12个月，很显然，求间隔月数的公式为：

=12*(year(截止日期)-year(开始日期))+(month(截止日期)-month(开始日期))

有时我们解决问题不一定要直接解决，进行一定的化归转化可以更简单地解决问题。

3. 把用.录入的类日期文本转换成日期

有时分发给他人的工作表返回时会发现需要录入日期的单元格被录入了“2000.5.1”这样的形式。Excel是无法自动识别这种格式的。对此我们可以简单地用CTRL+F召唤出查找替换对话框，用“-”替换“.”即可。

4. 限制用户必须录入日期

为了避免第3点中提到的问题，我们可以限制其他人必须录入一个日期类型数据，甚至可以约束录入的日期类型的起止范围。具体地：

第一步，打开数据有效性/数据验证对话框：

图03 打开数据验证对话框：

第二步，在弹出的数据有效性/数据验证对话框中，将“允许”设置为日期，将“数据”设置为自己需要的类型。例如这里我们设置“介于”后可以通过设置开始日期和结束日期的方式来约束单元格可以录入的日期范围。

图04 数据验证对话框：

通过设置单元格的数据有效性，我们可以约束其他人的数据录入行为，避免产生录入数据类型、值等的错误。

以上就是EXCEL中日期类型的简单介绍。