点击上方蓝字关注 Excel函数与公式
关注后发送函数名称,即可获取对应教程
本文关键字:datedif
Excel中最具价值的日期函数,非她莫属!
工作中经常会遇到涉及日期计算的问题,比如计算两个日期之间的天数、月数、年数。处理这类问题时都需要用到Excel日期函数。
大家熟知的YEAR、MONTH等日期函数无法搞定这类日期间隔的计算,这时需要用到Excel中一个极具价值的日期函数,同时她也是Excel中的隐藏函数,你在微软提供的的函数列表里是找不到她的,连帮助文件中也没有相关说明,她就是DATEDIF。
为了让大家认识DATEDIF函数那些不为人知的强大功能,本文贴合办公实际场景,整理了多种DATEDIF函数的应用方法。
1 DATEDIF函数语法解析及基础用法
DATEDIF函数是一个Excel中的隐藏函数,虽然在Excel中的函数列表中找不到这个函数,甚至帮助文件中也没有相关说明,但是DATEDIF函数是一个功能十分强大的日期函数,在工作中的应用非常广泛,用于计算两个日期之间的天数、月数或年数。
其基本语法为:
DATEDIF(start_date,end_date,unit)
start_date:必需。代表时间段内的起始日期,可以是带引号的日期文本字符串,比如“2016-8-8”,也可以是日期序列值、其他公式或者函数返回的运算结果,比如DATE(2016,8,8)等等。
end_date:必需。代表时间段内的结束日期,结束日期要大于起始日期,否则将返回错误值#NUM!。
unit:必需。代表日期信息的返回类型,该参数不区分大小写,不同的unit参数对应返回的结果如下表所示。
Unit参数 | DATEDIF函数返回结果 |
Y | 日期时间段中的整年数 |
M | 日期时间段中的整月数 |
D | 日期时间段中的天数 |
MD | 日期时间段中天数的差。忽略日期中的月和年 |
YM | 日期时间段中月数的差。忽略日期中的日和年 |
YD | 日期时间段中天数的差。忽略日期中的年 |
结合下面的案例,介绍一下DATEDIF函数的前三种基础用法:
两日期相差天数
=DATEDIF(A2,B2,"d")
两日期相差月数
=DATEDIF(A2,B2,"m")
两日期相差年数
=DATEDIF(A2,B2,"y")
忽略月和年,两日期相差天数
=DATEDIF(A2,B2,"md")
忽略日和年,两日期相差月数
=DATEDIF(A2,B2,"ym")
忽略年,两日期相差天数
=DATEDIF(A2,B2,"yd")
2 DATEDIF函数根据身份证号计算年龄
大家都知道,身份证号码里信息量很大的,比如可以从身份证号算出来年龄。
那么如果要从大量的身份证号码中提取年龄,如何批量搞定呢?
下面结合一个案例来具体介绍:
C2单元格输入以下公式:
=DATEDIF(--TEXT(MID(B2,7,8),"0-00-00"),NOW,"y")
3 DATEDIF函数根据入职日期计算工龄
工作中经常要计算两个日期之间的间隔时间,DATEDIF函数就是处理这类问题要使用到的一个高频函数,虽然在Excel中的函数列表中找不到这个函数,甚至帮助文件中也没有相关说明。
但是DATEDIF函数是一个功能十分强大的日期函数,在工作中的应用非常广泛,用于计算两个日期之间的天数、月数或年数。
下面结合一个实际案例,介绍DATEDIF函数根据入职日期计算工龄(精确到几年几月几天)的方法。
上图所示表格中包含员工的入职日期和要计算工龄的截止日期,黄色区域输入公式进行计算。
D2单元格输入以下公式:
=TEXT(SUM(DATEDIF(B2,C2,{"y","ym","md"})*10^{4,2,0}),"0年00月00天")
4 DATEDIF函数根据入职日期计算工龄工资
很多企业都有工龄工资,即根据员工服务的年数计算对应的工龄工资。
下面结合一个实际案例,来介绍计算工龄工资的方法。
表格中包含员工的入职日期和计算工龄的截止日期,黄色区域需要写公式进行工龄工资的计算。
计算工龄工资的规则有两条:
1、 员工每满一年,加50元工龄工资
2、 员工的工龄工资上限是20年,超出20年以后也按20年计算。
在D2单元格输入以下公式:
=50*MIN(20,DATEDIF(B2,C2,"y"))
5 DATEDIF函数实现生日提醒
DATEDIF函数不但可以直接统计出两个日期间隔的年数、月数、天数,而且还有很多延伸应用,比如实现生日提醒功能。
上图案例中,要Excel实现根据员工的出生日期,在10天内实现生日提醒。
C2单元格输入以下公式:
=TEXT(10-DATEDIF(B2,NOW+10,"yd"),"0天后生日;;今日生日")
6 DATEDIF函数自定义规则计算服役年数
DATEDIF函数配合其它函数,还可以实现比较复杂的计算。
表格中包括员工的服役日期、和计算日期,要计算服役年数,要求结果准备到0.5年。
规则:
1、 零头不足整年的,满6个月算1年
2、 1天至6个月算0.5年
D2单元格输入以下公式:
=CEILING(DATEDIF(EDATE(B2,-1)+1,C2,"m")/12,0.5)
还想看更多精彩?长按识别下图二维码,里面有各种方向不同的Excel特训营,每一期特训营内容不同,都可以帮你系统提升自己!
长按下图 识别二维码点关注,获取更多资料
长按上图↑识别二维码 点关注,查看详情
如果你觉得有用,就分享给朋友们看看吧~
閱讀更多 Excel函數與公式 的文章