[E2-06]日期时间函数
高考结束的时候，天气晴朗，你穿过门口等待的焦灼的人群，搭乘公交车回家，家里有爸爸妈妈还有那条叫做“欢欢”的二哈等着你。
大二上学期的某个上午，你像平常一样带着高数作业走到图书馆，像强迫症一样每天坐在相同的位置。那天唯一的不同是你的座位旁边提前有一位女孩子在那儿看书，你坐下的时候，她冲你礼貌性的笑笑算是打了招呼。
大四下学期，你开始纠结找工作的事情。想想和同学去成都的旅行经历，拥挤的人潮，颠簸的硬卧……还是算了吧，最终你选择了江南某二线城市。
有些事情，当时发生的时候，整个世界都是平淡无奇的，并没有一次暴雨或者整夜失眠昭告不同。只是当你长大了回头看，高考结束决定了你的大学，图书馆的那个上午遇到了爱人，不愉快的旅行经历最终决定了工作所在的城市。
每一步选择，其实都是人生的巨变，只是当时，多见平凡。时间，真的就是这么伤感又温柔的存在。
于是，今天，我们好好聊聊 EXCEL 日期时间函数：）
1.日期基础知识
有关日期时间最基础的内容建议回看上一篇单元格格式及格式转换相关内容。本章主要讲函数，基础知识不会讲太细。
默认情况下，年月日之间的间隔符号是“/”和“-”，也支持混用，比如“2017-07/14"依然是能够被识别的。中文操作系统下，年月日形式的书写也能被识别，如以下公式可以将字符串转化为数值:
=--"2017年7月14日"
除此之外，像小数点“." 、句号" 。"，反斜线 ”\“均不是有效的日期间隔符。还有一类表面上合法实质不合法的日期，比如:
- 1899-7-12，非法，不支持1900年1月1日之前的日期
- 2017-2-30，非法，2月没有30日
- 14/7/2017，非法，不支持这种写法
如何识别单元格内容是否是合格的日期格式？可以使用下面的函数:
=TEXT(A1,"yyyy年mm月dd日;;;非法日期")
不规范的日期字符串，包括不被识别的间隔符以及8位连写形如“20170714”字符串通过分列功能就可以转化为标准日期，具体操作在前面文章中介绍过。如果是7位连写字符串如“2017714”分列也是无法识别的，可以用函数 =TEXT(A2,"#-0-00") 完成转化，当然8位连写形如“20170714”更加不是问题，使用函数 =TEXT(A2,"#-00-00")即可。
还有一种可能引起歧义的7位连写字符串，如“2017124”，到底是1月24日，还是12月4日，就要从数据源本身去解决问题了，不是函数能解决的。最后，使用=TODAY()函数可以获取动态的当前日期，使用=NOW函数可以获取动态的当年日期时间。
2.时间基础知识
EXCEL时间可以精确到千分之一秒，时间数据被存储为0.0-0.99999999(别数了，8个9)之间的小数，每一秒对应的值为1/24/60/60(想想为什么)，时间 00:00:00.000对应0.0，相应的23:59:59.999对应0.99999999。如果输入的时间，小时超过24，分钟或秒超过60，EXCEL将自动转换，但时分秒只能有一个超出，比如“9:65:30”会被自动转为“10:05:30”，“9:70:70”将被识别为普通字符串。
3.函数实现文本与日期时间格式互转
从标题就能出来我要讲什么了对吧，直接上案例。
- 简单的日期字符串转换上面已经讲过了，我们来个复杂点的，将字符串“20170714210524”转化为标准日期时间格式，函数是:
=--TEXT(A3,"#-00-00 00!:00!:00")
- 将标准日期时间格式转化为普通字符串。比如标准日期 2017-7-14希望转化为“20170714”使用函数:
=TEXT(A1，“yyyymmdd”)或者=TEXT(A1，“emmdd”)都行
4.日期构造与解析
所谓日期构造，就是给定年月日数据，生成一个标准日期；所谓日期解析，就是给定标准日期，分别返回年月日。
日期构造
日期构造函数DATE(year，month，day)，三个参数分别表示年，月，日的值。如果给定年月日对应的日期不存在，DATE函数会自作主张调整无效参数，得到新的日期而不是返回错误，要特别留意。如DATE(2017,4,31)，因为4月没有31日，则返回日期是2017/05/01，另外DATE函数对缺省参数是这么处理的:
- 年份缺省使用1900年。DATE(,4,12)返回1900年4月12日。
- 月份缺省为上一年的12月。DATE(2017,,12)返回2016年年12月12日。
- 日期(day)缺省默认上个月最后一天。DATE(2017,4,)返回2017年年3月31日。
日期解析
日期解析包括YEAR函数、MONTH函数和DAY函数。这三个函数分别返回年份(1900-9999)，月份(1-12)，日期(1-31)对应的整数值。他们对应的等价函数如下(如果需要都转化为数值的话，使用--运算即可):
=TEXT(A1,"Y")=TEXT(A1,"M")=TEXT(A1,"D")
5.时长计算
假设A1存储起始时间 “2017/7/12 21:05:20”，B1存储结束时间“2017/7/14 12:23:03”，我们需要计算两个时间之间的差值:
- 相差分钟数 =INT((B1-A1)* 1440)，其中1440=24小时 *60分钟。之所以使用INT取证是舍弃了差值的秒数部分。
- 相差秒数 =(B1-A1)* 86400,86400=1440*60.
计算时间间隔还有更快更便捷的方式:
- 间隔小时数: =TEXT(B1-A1，"[h]")
- 间隔分钟数: =TEXT(B1-A1，"[m]")
- 间隔秒数: =TEXT(B1-A1，"[s]")
6.DATEDIF
缺少主角，再好的戏也热闹不起来。文本处理函数的王者函数是TEXT，日期时间的荣耀压轴则是DATEDIF，这么重要的函数居然是隐藏的(公式编辑栏输入是没有提示的，帮助系统也没有对应支持)，微硬的攻城狮们，良心真的不会疼吗？
基本语法
DATEDIF函数用于计算两个日期之间的天数、月数或年数。，这个函数的基本语法一点儿不复杂:DATEDIF(start_date,end_date,unit)，其中:start_date代表起始日期，可以是文本字符串如“2017/07/14"，也可以是标准日期值或函数计算返回的结果如DATE(2017,7,14)。end_date类似，不过有一个要求就是end_date要晚于start_date，否则函数将返回错误。有趣的是第三个参数unit，这个参数直接决定了函数以什么方式返回计算结果，具体如下:
- Y，返回整年数。
- M，返回整月数。
- D，返回天数。
- MD，仅比较日期中天数的差，忽略月和年。
- YM，仅比较日期中月份的差，忽略年和日。
- YD，仅比较日期中天数的差，忽略日期中的年。按照月，日计算天数。
工龄计算
假设A列存储的是员工的参加工作日期，以当前时间(2017年7月)为准，我们需要计算所有员工的工龄，计算规则是当前日期减去参加工作日期后相差的年数，不足一年的部分将被舍去。同时，工龄封顶最多算50年。公式为:
=MIN(50，DATEIF(A1,"2017/07/14","Y"))
生日提醒
假设A列存储的是员工的出生日期，我们需要实现员工日期不足7天的时候自动提醒，对照上面unit参数表，首先我们知道计算离生日还有几天的时候，和年是没有关系的，所以应该使用“YD”参数，完整公式如下:
=TEXT(7-DATEDIF(A1-7,TODAY(),"YD"),"离生日还有0天;;今天生日")
计算结果如下图:
这个公式一定要解释一下的。首先看 DATEDIF(A1-7,TODAY(),"YD")部分，既然要求提前7天提醒，那么我们关心的是当前日期和出生日期之前7天的关系。TODAY()返回的日期肯定是晚于左边列任何值这没有问题，那么这个DATEDIF返回值只有三种情况:
- =7，说明生日就是今天。
- >7，说明离生日那天的天数大于7天，也就是还不用提醒。
- <7，说明离生日越来越近了，要提醒了。
有了这个结果，那就好办了，用7减去上述函数的返回值，对应的结果一定是正数，负数或者0.正数说明要提醒，负数什么也不用干，0说明当天生日。我们根据这个结果给出标准的四段式自定义单元格格式代码 "离生日还有0天;;今天生日"即可。应用格式代码要用上一章讲的TEXT函数，整个函数就比较清晰了。
全文完。
- - - - - - - - - - - -
如果这篇文章对您有帮助，可否帮我转发朋友圈让更多人看到？多谢!
关注公众号“怒马说”，第一时间阅读EXCEL干货。
