刚好有两个读者对获取日期间隔数有疑问,卢子今天就进行详细说明。
1.如何计算两个日期之间相差的天数?
日期其实就是特殊的数字,数字是可以进行加减运算,两个日期相差的天数就是当天的日期减去入库的日期。在运算的时候,计算出来的天数是以日期的形式显示,需要再将单元格设置为常规格式。
有的时候,我们用VLOOKUP函数查找日期,公式设置完后将单元格设置为日期格式。这时会看见1900/1/0这样的日期,怎么回事呢?
当查找的对应值是空白单元格,就会返回0,而0设置为日期格式就是1900/1/0,数字1的日期就是1900/1/1。
在Excel中的日期起点是1900/1/1,不允许日期在1900之前,比如1899/12/1其实不能算日期,只能算一个文本内容。如果你不信,可以用ISNUMBER函数判断,如果是数字就返回TRUE,否则就返回FALSE。
将日期设置为常规格式,就可以看到相对应的数字。
同理,时间也是数字,也可以进行四则运算。
不过时间在相加的时候,需要注意,当时间超过24小时的时候,运算会出错。一周上班总时间很明显不是4:45,针对这种问题又该如何解决?
嵌套一个TEXT函数,[h]就表示超过24小时显示实际小时数。
2.如何计算两个日期之间相差的月份和天数?
从上一个案例知道,日期是数字,可以直接进行四则运算。但是,问题来了,如果直接用四则运算,是没法判断两个日期相差几个月,因为每个月的天数是不固定的。
这时就出现一个DATEDIF函数,这个函数可以获取两个日期相差的年月日。这是隐藏函数,跟普通函数有所区别,在输入的时候没任何提示。即使你输入date也看不到任何关于这个函数的信息,所以当你输入函数的时候,找不到是很正常的。
计算两个日期相差的年月日,y代表年,m代表月,d代表日。
=DATEDIF(A2,B2,"y")
=DATEDIF(A2,B2,"m")
=DATEDIF(A2,B2,"d")
不过这种算法又有一个问题,在计算月的时候没有忽略年,在计算日的时候没有忽略月。因此,出现了一种新的方法,ym代表忽略年计算月,md代表忽略月计算日。
=DATEDIF(A2,B2,"y")
=DATEDIF(A2,B2,"ym")
=DATEDIF(A2,B2,"md")
现在要计算两个日期相差多少个月多少天,只需要将刚刚说的知识点结合起来,用&起来即可。
=DATEDIF(A2,B2,"m")&"个月"&DATEDIF(A2,B2,"md")&"天"
其实,公式并不难,不要想着一步登天,慢点再慢点,坚持跟着卢子一起学习,你会学到很多有用的知识。
推荐:9个Excel日期小常识
你学Excel多久了,在学习的过程中有什么有趣的事,分享一下。
作者:卢子,清华畅销书作者,《Excel效率手册 早做完,不加班》系列丛书创始人,个人公众号:Excel不加班(ID:Excelbujiaban)
联系客服