pb中计算两个时间的分钟_还把时间浪费在考勤上？教你两个公式，1分钟整理好考勤数据...

每个公司都离不开员工考勤&＃xff0c;然而每次从考勤机导出考勤数据的时候&＃xff0c;我们都会耗费大量的时间&＃xff01;今天和大家分享两个公式&＃xff0c;考勤数据一分钟就可以整理好&＃xff01;

我们平时从考勤机导出的考勤数据&＃xff0c;通过Excel打开后基本都是这样的格式&＃xff1a;

为了进一步统计考勤数据&＃xff0c;希望将上面的这种格式整理为这样的效果&＃xff1a;

每人每天的打卡记录在同一行&＃xff0c;并且根据上下班时间比对后标注出异常情况。

上下班时间规定如下&＃xff1a;

上午上班时间8:00&＃xff0c;上午下班时间12:00&＃xff0c;下午上班时间13:30&＃xff0c;下午下班时间17:30

要实现这种效果的转换&＃xff0c;感觉是非常麻烦的一件事&＃xff0c;其实只要掌握两个公式和一些基本的操作技巧&＃xff0c;一分钟就可以完成&＃xff0c;下面就来看看如何实现吧。

1、基础数据整理

在基础数据的右边添加几列&＃xff0c;将时间分为上午上班、上午下班、下午上班和下午下班四列&＃xff0c;并且标注出对应的时间&＃xff1a;

将卡号、人员和日期三列复制到右边对应的位置&＃xff0c;然后使用“删除重复项”功能&＃xff1a;

点击确定后会删除重复的内容&＃xff0c;每人每天只保留一行&＃xff1a;

接下来的任务就是将对应的打卡时间填入对应的位置&＃xff0c;并且对异常数据不显示具体时间&＃xff0c;只显示异常两个字。为了实现这个目的&＃xff0c;需要使用两个公式来配合&＃xff0c;下面先看第一个公式。

2、使用公式备注打卡时间

为了便于对打卡时间进行统计&＃xff0c;首先要根据上下班时间进行备注&＃xff0c;实现下图中的效果&＃xff1a;

根据上下班时间需要分为四种情况&＃xff1a;

1、8点以前打卡视为上午上班&＃xff1b;

2、12点以后打卡视为上午下班&＃xff0c;考虑到还有下午上班这个因素&＃xff0c;人为规定12点到12点30之间打卡为上午下班&＃xff1b;

3、同理&＃xff0c;人为规定13点到13点30之间打卡为下午上班&＃xff1b;

4、17点30以后打卡为下午下班&＃xff1b;

5、除此之外的时间打卡均为无效&＃xff0c;显示空白。

E2单元格公式为&＃xff1a;

&＃61;IF(D2<&＃61;$K$1,$K$2,"")&IF(AND(D2>&＃61;$L$1,D2<&＃61;$O$1),$L$2,"")&IF(AND(D2>&＃61;$P$1,D2<&＃61;$M$1),$M$2,"")&IF(D2>&＃61;$N$1,$N$2,"")&＃xff0c;双击填充可实现图中的效果。

第一个IF为&＃xff1a;&＃61;IF(D2<&＃61;$K$1,$K$2,"")

当d2(打卡时间)小于等于k1(上午上班时间)时&＃xff0c;if函数的结果为k2(上午上班这四个字)&＃xff0c;否则返回空值&＃xff1b;

第二个if为&＃xff1a;IF(AND(D2>&＃61;$L$1,D2<&＃61;$O$1),$L$2,"")

当d2(打卡时间)大于等于L2(上午下班时间)同时小于等于o1(人为规定下班打卡截止时间)时&＃xff0c;if函数的结果为L2(上午下班这四个字)&＃xff0c;否则返回空值。

第三个if为&＃xff1a;IF(AND(D2>&＃61;$P$1,D2<&＃61;$M$1),$M$2,"")

当d2(打卡时间)大于等于p2(人为规定上班打卡开始时间)同时小于等于M1(下午上班时间)时&＃xff0c;if函数的结果为M2(下午下班这四个字)&＃xff0c;否则返回空值。

第四个if为&＃xff1a;IF(D2>&＃61;$N$1,$N$2,"")

当d2(打卡时间)大于等于N1(下午下班时间)时&＃xff0c;if函数的结果为N2(下午下班这四个字)&＃xff0c;否则返回空值。

完成了备注信息之后&＃xff0c;就该把对应的时间填入对应的区域内&＃xff0c;这时候可以用一个公式右拉下拉就能完成时间的填充&＃xff0c;一起来看看是哪个神奇的公式吧。

3、填充时间

在K2单元格输入公式&＃xff1a;

&＃61;TEXT(SUMIFS($D:$D,$C:$C,$J3,$A:$A,$H3,$E:$E,K$2),"hh:mm:ss;;异常;")

右拉下拉即可完成时间的填充。

这个公式用到了两个函数&＃xff0c;text和sumifs&＃xff0c;来看看公式的原理吧。

sumifs函数的结构为sumifs(要求和的数据区域&＃xff0c;条件区域1&＃xff0c;条件1&＃xff0c;条件区域2&＃xff0c;条件2……)&＃xff0c;在今天的例子里我们用了三个条件&＃xff0c;实际求和的是D列&＃xff0c;三个条件分别是日期、卡号和备注信息&＃xff0c;符合三个条件的数字都是唯一的&＃xff0c;所以求和结果和引用结果是一致的。

因此公式为&＃xff1a;

SUMIFS($D:$D,$C:$C,$J3,$A:$A,$H3,$E:$E,K$2)

使用公式得到结果是这样的一些数字&＃xff0c;因为在Excel中&＃xff0c;日期和时间的本质都是数字&＃xff0c;整数代表日期&＃xff0c;而小数就代表时间&＃xff0c;将上述区域单元格格式改为时间再看看效果&＃xff0c;数字都变成了具体的时间&＃xff0c;如下所示&＃xff1a;

实际上在进行了单元格格式设置后基本就达到目的了&＃xff0c;为了完善显示效果&＃xff0c;同时强制显示为时间格式&＃xff0c;我们在sumifs外面再加了一个text函数&＃xff0c;即使在常规格式下&＃xff0c;也是按时间来显示的&＃xff0c;同时0所在的位置显示为异常。

简单解释一下text的用法&＃xff0c;text(数据&＃xff0c;指定的格式)&＃xff0c;在本例中&＃xff0c;第二参数格式定义为时分秒的显示方式&＃xff0c;字母h、m和s分别表示时分秒&＃xff0c;都是两位数字显示。

格式代码中的分号&＃xff0c;可以按照数据类型单独设置显示方式&＃xff0c;text规定将数据分成四种&＃xff1a;正数;负数;零;文本。本例中正数按照时间格式显示&＃xff0c;负数和文本没有指定格式就不显示&＃xff0c;而零显示为异常两个字。

End.

编辑&＃xff1a;南北(牛账网小编)

牛账网&＃xff0c;为您提供会计实操干货与考证经验分享、2000&＃43;们免费教学视频及题库等&＃xff0c;让您所学及所用。

公众号&＃xff1a;牛账会计

免责声明&＃xff1a;本文内容仅供阅读者参考&＃xff0c;具体以相应法规及当地行政机关判定结果为准。引用或转载&＃xff0c;请注明以上信息。

点击下方“了解更多”领取免费实操课程/0基础入门课程&＃xff01;