上次我们说到了Excel的日期的隐藏BUG,但事实上,这个BUG在日常工作中能遇到的概率比较小,很少有人计算跨越1900年2月29日前后两个日期的差值。
但事实上,我们是经常要计算两个日期的差值的,比如说计算年龄(当前日期和出生日期的差值)、工龄(当前日期和工作日期的差值)、任现职时间等。 我们就用以身份证号为基础,计算年龄的例子分享一下Excel中计算两个日期间隔的方法: 一、在工作表中的一般做法最常见的思路: 第一步:先用mid函数分别取得身份证号中的年份、月份和日期的字符串。
第二步:再用Date函数转换成日期
第三步:再算和TODAY的差值,得出来的结果是相差的天数。在这里可以将单元格格式设置为常规,方便阅读。
第四步:再用天数除以除以365.25得出相差的年数
第五步:用INT或者TRUNC或者ROUNDDOWN向下取整
这种方式是最容易想到并理解的思路,但是总感觉这样做不太灵活,如果要算经过的周数可以用天数除以7,如果算经过的月数或者季度数,这个公式就有点捉襟见肘了。 二、工作表的隐藏函数DATEDIF在Excel中,存在那么几个隐藏函数,在单元格输入这些函数时,并不能联想提醒输入,但是确实Excel又存在这么几个函数。DATEDIF就是一个,用于计算两个日期的差值。
Start_date 为一个日期,它代表时间段内的第一个日期或起始日期。可以是日期格式的文本,比如说”1987-01-05”,也可以是日期对应的数值,比如1900年1月1日对应的数值为1。 End_date 为一个日期,它代表时间段内的最后一个日期或结束日期。 Unit为该公式的返回结果类型:
用DATEDIF函数,就可以这么计算日期差值: 第一步:用MID取得出生日期的文本,并用TEXT转化成日期格式的文本
第二步:直接用DATEDIF函数得到出生日期和当前日期相差的年份
在这样的基础上,如果想求相差的月份,只需要将unit参数的”Y”换成”M”即可,相比第一种方法,还是会简单快捷不少。 三、在VBA中计算两个日期的间隔在VBA中,计算两个日期间隔的天数依然可以直接相减求得。 如果要计算两个日期间隔的年、月、周等,在VBA中可以利用内置函数DateDiff进行计算。DateDiff函数和工作表中的DATEDIF函数很像,语法如下:
firstdayofweek和firstweekofyear两个可选参数,主要在计算间隔的周数上能用得到。 firstdayofweek用来指定一个星期的第一天是周几,默认星期天是第一天。 Firstweekofyear用来指定一年的第一周怎么算,可以指定包含1月1日的周为第一周,这也是该参数的缺省值;也可以指定从第一个其大半个周在新的一年的一周开始起算,参数值为2,;也可指定从第一个无跨年度的周开始起算,参数值为3。 因为这两个参数实际应用的比较少,我们这里也就不过多介绍了,有兴趣的小伙伴可以自己看一下帮助文档做一下试验。 今天我们分享了在Excel工作表和VBA中计算两个日期间隔的方法,虽然比较简单,但是还是比较常用的,有些初学的小伙伴就不用再用复杂的办法计算年龄——比如用IF条件判断月份大小,从而决定是否在年份的差值加1这样的办法。希望小伙伴们应用起来,在工作中提高效率。 |
|