微优网Excel攻略板块,专业的Excel攻略站!

|返回首页|网站地图|

当前位置:首页office攻略Excel攻略→Excel计算考勤时间,一个公式轻松搞定~

Excel计算考勤时间,一个公式轻松搞定~

时间:2020-07-28人气:147作者:微优网Excel攻略

在日常工作中,考勤是表格处理中一个大难题且有很多种问题,这个需要具体问题具体分析。如果数据少可以手动计算和判断,但如果数据量大,自己计算和判断需要花很多时间,这怎么办呢?

今天来举个计算规则较为明确的例子。来看一位小伙伴遇到的实际问题,数据源及结果如下:

这个问题大致意思是:这个表需要统计去除周六日的时间,另外需要去除工作日21点至第二天9点的时间——也就是说每个人的“天”的考核按照12小时来计算,时间的结果计算成小时来统计每个人的人工,模拟结果如C列所示。

这个问题不难理解,但是猛地一看无法下手。你思考一下,可能你会有一种灵光一闪的感觉:

线

这个问题的思路是这样的:

我们把数据源的时间和日期分别分析,发现时间可能是前小于后,也可能是前大于后,那么我们把时间和日期分开计算。日期我们可以用NETWORKDAYS函数来计算工作日,然后再乘12得到总时长整数部分。时间我们可以分类讨论:A列小于B列和A列大于B列。

NETWORKDAYS简介

NETWORKDAYS是计算两个日期之间的工作日的天数。

格式:NETWORKDAYS(起始日期,截止日期,假期)

第三参数可以省略。若省略,则没有自定义假期(包括国家法定节假日)单纯去除周末计算工作日的天数。

若起始日期大于截止日期,则会出现num!错误。

说了这个语法,有朋友就兴高采烈的尝试=NETWORKDAYS(A2,B2)*12来计算A和B之间的工作日天数。但输入后下拉发现以下情况:

第一行时间差较大,不方便计算。我们手工计算第二行的工作日,发现应当是一天,这里显示24小时,相当于两天,这是为什么呢?

这是因为NETWORKDAYS计算天数是把起始日期和结束日期算上的,就是说比如今天7月24日,那么NETWORKDAYS(7月24日,7月24日)=1(非严谨写法,仅用于解释特性)。

根据上述特性,我们可以把公式改正为:=(NETWORKDAYS(A2,B2)-1)*12,下拉后结果如下:

好了,介绍了NETWORKDAYS基本用法后,我们介绍时间和日期的本质。

在Excel中,日期是整数,时间是小数。Excel里把时间除24后存储。如12:00:00=12/24=0.5,6:00:00=6/24=0.25。日期是从1900年1月1日开始计算,将1900/1/1存储为1。(把A,B列改成常规格式即可看出)

故计算时间的问题变成计算小数的问题。计算小数有比较多的方法,这里采用mod(a2,1)的方法。mod(a2,1)的意思是:对A2/1取余数。如果A2是整数,那么mod(a2,1)=0;如果A2是小数,那么mod(a2,1)等于小数部分。

当A列时间小于B列时间时,总时间就等于(NETWORKDAYS(A2,B2)-1)*12 MOD(B2,1)-MOD(A2,1)。意思是a2到b2的工作日乘以12加上两列的时间差(即MOD(B2,1)-MOD(A2,1),指的是时间大的减时间小的,下同);

当A列时间大于B列时间时,总时间就等于(NETWORKDAYS(A2,B2-1)-1)*12  12-(MOD(A2,1)-MOD(B2,1))。意思是我先计算a2到b2的前一天有多少工作日,然后计算出两列时间差(即MOD(A2,1)-MOD(B2,1))。然后由于一天按12小时计算,所以我就用12-(MOD(A2,1)-MOD(B2,1))计算实际时间差(不同于之前提到的时间差),加起来就是(NETWORKDAYS(A2,B2-1)-1)*12 12-(MOD(A2,1)- MOD(B2,1)),化简后得NETWORKDAYS(A2,B2-1)*12 MOD(B2,1)-MOD(A2,1)

故完整公式及下拉后结果如下:

=IF(MOD(B2,1)

大功告成!

有兴趣的小伙伴可以下载数据源下来多研究研究。

相关攻略

  • 给Excel安装监控,专治不按要求填数据

    今天老祝要和大家一起学习一个非常简单,但是十分实用的小技巧。 在下图的员工信息表中,要求各个记录必须是连续输入的,如果输入的不完整或是输入后又删除了记录, Excel 就不..
  • Excel插件:快速制作属于自己Excel插件

    Excel插件:快速制作属于 自己的 插件,建立批量操作工具箱! 工作中处理工作表时常常会遇到一些比较复杂的问题,相信大家都遇到过,比如一键创建工作表目录链接、一键拆分工作..

Copyright 2019-2022 www.vyyoo.com 【微优网】 版权所有

声明:本站部分文章来自互联网 如有异议 请与本站联系 本站为非赢利性网站 不接受任何赞助和广告