【Excel】不知道怎样计算加班天数？来这我教你呀~

大家好，今天给大家介绍一个老式打卡机的问题，数据源和模拟结果如下：

这个小伙伴想算这个月的加班天数，以便发加班补贴。他们的加班是每天21:00以后到次日6点前的打卡算作加班卡（时间后面含有外勤两个字的不计入）。模拟结果如AN列所示。

举个例子，刘烁绯这个人仅在4号和7号两天在21:00以后打卡，所以AN6=2。

这个表就是上次展示的那个表，我已经把数据源手工调好了。

这个问题比较难，给大家2分钟的时间考虑。

2

分

钟

的

分

割

线

这个问题相信很多人一看到数据源啥规律都找不到就想砸电脑了。大家不要急，我们把某个单元格取消自动换行看看。

咦？这个竟然是有“空格”隔开的数据，而且时间都是五位。

观察完数据以后，我想到一个思路：我们判断每天的最后一个时间（即去掉“空格”的最后五位）是不是在21:00以后到次日6点前，如果是就标记1，不是就标记0。

我们可以用SUBSTITUTE函数把所有单元格中的“空格”（这个“空格”需要从单元格复制，不能打键盘上的空格键）去掉，然后用RIGHT函数取最后五位，转换为数值以后如果大于21:00或者小于6点，则记为1

在EXCEL中，对逻辑值进行或运算可以用加法，故我们可以把这个思路写成(IFERROR(--RIGHT(SUBSTITUTE(D4:AH4,' ',''),5),0)>TIME(21,0,0))+(IFERROR(--RIGHT(SUBSTITUTE(D4:AH4,' ',''),5),0)

注：套IFERROR函数是为了排除文本空的错误，因为文本空形成的错误不能和时间比较。

由于空白单元格按照0计算，而0肯定小于6点，这样结果就会把空白单元格的个数计算进去。故我们要对上面的式子加一个并且的判断条件：D4:AH4<>''

在EXCEL中，对逻辑值进行且运算可以用乘法。

乘起来以后，完整公式如下：

=SUM(((IFERROR(--RIGHT(SUBSTITUTE(D4:AH4,' ',''),5),0)>TIME(21,0,0))+(IFERROR(--RIGHT(SUBSTITUTE(D4:AH4,' ',''),5),0)''))

效果如下图：（这个公式需要按CTRL+SHIFT+ENTER三键结束）

当然，也可以把SUM换成SUMPRODUCT（如文件的模拟结果AP列所示），这样按ENTER结束就可以了哦。

根据这个思路，我们还可以计算每个人的上班总时长，有兴趣的可以思考一下，然后下载文件看模拟结果的AO列。

写在最后：这种样式的考勤表处理数据比较麻烦。这个打卡机虽然说有BUG，但是因为人名均在第一列，所以这样的数据可以对某些计算结果进行透视汇总之类的操作。我们下面介绍的一个考勤问题，考勤表的样式是这样的：

这样的考勤表比较适合打印分给每个员工，不太适合处理数据。

后面我会花一个篇幅的时间写下关于这个表的一个问题，敬请期待。