学会这些时间和日期计算套路,效率还能再高些!

发布时间:2020-01-07 来源:网络


今天技巧妹要跟大家分享一波时间和日期的计算套路。


计算间隔的分钟数


计算两个具体时间之间间隔的分钟数,可以直接用结束时间减去起始时间,再乘以1440即可。



计算间隔的小时数


计算两个具体时间之间间隔的小时数,也是直接用结束时间减去起始时间,再乘以24即可,结果这里取一位小数。



时间和日期的合并及拆分


日期和时间的合并很简单,直接用+符号连接就可以,注意需要把合并内容所在的单元格格式自定义设置为“yyyy/m/d h:mm:ss”。



如果把日期和时间进行拆分,提取日期则用到INT函数,向下取整为最接近的数值,这里把时间视为小数部分,如果时间满24小时,则会进位1天,提取时间直接减去日期即可。


计算加班时间


部分企业计算员工的加班时间,是遵循这样的原则:不足半小时直接舍去,超过半小时的部分则按照半小时计算,这里我们可以利用FLOOR函数进行向下舍入,计算加班时间。输入公式:=FLOOR((B2-A2)*24,0.5)


公式说明:FLOOR函数是向下舍入(沿绝对值减小的方向)为最接近基数的倍数,它的语法结构为=FLOOR(数值,倍数)。(B2-A2)*24,结束时间减去开始时间,以天为计算单位,将差值乘以24,计算出加班的小时数,最后用FLOOR函数将小时数向下舍入到最接近0.5的倍数,即为加班工时。


计算日期所在月份的总天数


用EOMONTH函数和DAY函数结合计算指定日期当月的总天数,输入公式:

=DAY(EOMONTH(A2,0))



公式说明:EOMONTH函数返回日期所在月份的最后一天,再利用DAY函数返回该日期是这个月第几天,也就是这个月的总天数。


判断上中下旬


判断指定日期是上旬、中旬或下旬,可以利用LOOKUP函数的区间查找功能。输入公式:=LOOKUP(DAY(A2),{0,11,21},{"上旬","中旬","下旬"})



公式说明:先用DAY函数判断提取出该日期为本月的第几天,然后用LOOKUP函数进行区间的近似匹配。第二个参数是一个常量数组,为上中下旬的最小日期值,查找小于或等于第一个参数的最大数值,返回第三个参数中对应位置的值。


判断日期所属季度


判断指定日期所属的季度,这里我们演示3个比较常用的函数公式。


1、首先直接用IF函数进行月份判断,返回不同季度,输入公式:


=IF(MONTH(A2)<=3,"第1季度",IF(MONTH(A2)<=6,"第2季度",IF(MONTH(A2)<=9,"第3季度","第4季度")))



2、利用INT函数进行判断,输入公式:


="第"&INT((MONTH(A2)+2)/3)&"季度"



公式说明:先把月份加上2再除3后,用INT函数取整,即可得到所属季度。INT函数是直接抹去小数取整,不进位。


3、最后用到的是CEILING函数,输入公式:


="第"&CEILING(MONTH(A2)/3,1)&"季度"



公式说明:CEILING函数语法结构为=CEILING(需要舍入的数值,取整基数),是用来按指定基数大的倍数向上取整。