一、先看这张表:B2、C2 里到底是什么
假设你的原始打卡表长这样,A 列姓名、B 列上班卡、C 列下班卡,第 1 行是表头,第 2 到 4 行是三个人:
| 行 | A:姓名 | B:上班卡 | C:下班卡 |
|---|---|---|---|
| 1 | 姓名 | 上班卡 | 下班卡 |
| 2 | 张三 | 2026-03-10 09:00 | 2026-03-10 18:00 |
| 3 | 李四 | 2026-03-10 20:00 | 2026-03-11 08:00 |
| 4 | 王五 | 20:00 | 08:00 |
三行看起来像同一件事,其实有两种数据形态:张三和李四是完整日期时间(带 2026-03-11 这个日期),王五是纯时分(只有 20:00 和 08:00,没有日期)。这个差别就是后面所有问题的根源,后面每个公式我都会指明它在哪一行、引用哪两格。
46091.833333 这种带整数部分的是完整日期时间;显示成 0.833333(小于 1)的是纯时分。二、负数从哪来:时间在 Excel 里是小数
Excel 把一整天当成 1,时间就是这一天里的小数。所以 09:00 是 0.375、20:00 是 0.833333、次日 08:00 是 1.333333(如果带日期)或 0.333333(如果不带)。
| 单元格 | 显示内容 | 实际存储值 | 拆开看 |
|---|---|---|---|
| B3 | 2026-03-10 20:00 | 46091.833333 | 日期 46091 + 时间 0.833333 |
| C3 | 2026-03-11 08:00 | 46092.333333 | 日期 46092 + 时间 0.333333 |
| B4 | 20:00 | 0.833333 | 只有时间,没有日期 |
| C4 | 08:00 | 0.333333 | 只有时间,没有日期 |
于是两行的差别就出来了:
- C3 − B3 = 0.5(46092.333333 − 46091.833333),乘 24 得 12 小时,正确;
- C4 − B4 = −0.5(0.333333 − 0.833333),乘 24 得 −12 小时,负数。
所以负数的成因不是"夜班特殊",而是这一行的两个时间都没有日期,Excel 只能认为 08:00 发生在 20:00 之前。同一张表里两种形态混着,是最常见的状况——考勤机导出的一个格式带日期,钉钉、企微导出的时分列不带。
三、IF 判断加一天:逻辑对,但要写好几遍
最直觉的修法是"下班时间比上班时间早,就给它加一天"。以王五这一行为例,公式写在 D4:
=IF(C4<B4,C4+1,C4)-B4
' C4 = 0.333333(08:00),B4 = 0.833333(20:00)代进去走一遍:0.333333 < 0.833333 成立 → C4+1 = 1.333333 → 减 B4 得 0.5 → 乘 24 得 12 小时。修好了。
同一套逻辑套到李四那行(D3)也能算对,因为 C3 本来就大于 B3,IF 走的是"不加一天"这条支路,结果 0.5。但它的问题不在逻辑,在重复次数:两次打卡的班次写 1 个 IF 就够;4 段打卡(上下午各一对)要写 4 个;6 段打卡要写 6 个。
关键在第三列:负数不会跳出来报错,它会安静地混在求和里。4 段打卡的表有 4 个公式,谁改错一处括号、哪天下班卡晚打了 5 分钟,那段就悄悄变负,等到月底总数差了十几小时才发现。
四、MOD:一行搞定,逐格代一遍
把"该不该加一天"这个判断交给函数自己做,公式写在 D 列任意一行,这里是 D3(李四):
=ROUND(MOD(C3-B3,1)*24,2)
' C3 = 2026-03-11 08:00(46092.333333),B3 = 2026-03-10 20:00(46091.833333)| 步骤 | 表达式 | 代入后的值 |
|---|---|---|
| ① 相减 | C3 − B3 | 0.5 |
| ② 取一天内的余数 | MOD(0.5, 1) | 0.5 |
| ③ 换成小时 | 0.5 × 24 | 12 |
| ④ 保留两位 | ROUND(12, 2) | 12 |
再换王五那行(D4),相减得 −0.5,MOD(−0.5, 1) 得到 0.5,结果还是 12。负数为什么自己变正?因为 Excel 的 MOD 结果符号跟随除数,而不是被除数;除数是 1,结果就永远落在 0 到 1 之间。
三行一起套上,左边是直接相减、右边是 MOD,差别一目了然:
| 行 | 姓名 | C − B | 直接相减 ×24 | MOD(C−B,1) ×24 |
|---|---|---|---|---|
| 2 | 张三 | 0.375 | 9 | 9 |
| 3 | 李四 | 0.5 | 12 | 12 |
| 4 | 王五 | −0.5 | −12 | 12 |
看清楚这张表的意义:MOD 不改变正确结果,只把错的那个掰回来。张三、李四用 MOD 前后都是 9 和 12,王五从 −12 变成 12。所以你不需要先判断表里是哪种形态,一条公式从头拖到尾就行。
2026-03-10 20:00)会让 MOD 报 #VALUE!。先"分列"或用 =--B3 转成数值,再套公式。五、还有该扣的午休和晚餐
MOD 算出的是"进厂到出厂"的跨度,不是有效工时。接着往右加三列:D 跨度工时、E 午休、F 晚餐,再补一个人赵六(第 5 行,长白班 08:00 到次日 00:00):
| 行 | B 上班 | C 下班 | D 跨度工时 | E 午休 | F 晚餐 | G 有效工时 |
|---|---|---|---|---|---|---|
| 3 | 20:00 | 08:00 | 12 | 0 | 0.5 | 11.5 |
| 5 | 08:00 | 00:00 | 16 | 1 | 0.5 | 14.5 |
=ROUND(MOD(C3-B3,1)*24,2)-E3-F3
' E3 = 0(这个夜班没有午休),F3 = 0.5(宵夜半小时)夜班那行:12 − 0 − 0.5 = 11.5 小时;长白班那行:16 − 1 − 0.5 = 14.5 小时。注意 E3 填的是 0 而不是留空——减一个空单元格,整条公式会变成 0 或错误值,很多人公式看着没错、结果全空,就是这里。
还有一点:休息不是所有班次都有。8 小时白班没有晚餐,16 小时连班午休晚餐都要扣。如果不管班次长短一律减 1.5 小时,短班的人每天少算半小时,一个月就是十几个小时。
六、工时对了,日期还是错的
MOD 解决了"这一班有多长",但没解决"这一班算哪一天"。李四的上班卡在 3 月 10 日、下班卡在 3 月 11 日,如果你按下班卡的日期筛选,这一班会被归到 3-11,3 月 10 日那行就变成"有上班没下班",判成缺卡。
加一列"归属日",规则是看上班卡,不看下班卡:
=IF(AND(HOUR(B3)<6, 前一天排的是跨凌晨班), INT(B3)-1, INT(B3))
' B3 = 2026-03-10 20:00,HOUR(B3) = 20,不满足<6,归属日 = 3-10两个条件都不能省。只看"上班时刻早于 6 点"会误伤三班倒里的第三种班——00:00-08:00 的夜班,上班卡本来就在凌晨 0 点,属于当天,不该往前推一天。必须再确认前一天确实排了跨凌晨班且还没结束,才回退。
七、4 个校验:别让 MOD 抹平脏数据
MOD 有个副作用:它把所有差值都压进 0 到 24 小时。打卡机要是把"次日 08:00"错存成"后天 08:00",真实差 36 小时,被 MOD 一压又变回 12 小时,看着完全正常。所以用了 MOD,就得配几条上限校验。
| 校验项 | 判据 | 说明什么 |
|---|---|---|
| 超长工时 | G 列 > 班次标准 + 4 小时 | 多半是日期错位,或下班卡打在了第二天 |
| 零/极短工时 | G 列 < 0.1 小时 | 两次打卡时间相同,重复记录 |
| 短于半班 | G 列 < 班次标准 ÷ 2 | 漏打了一张卡,不能直接按结果发工资 |
| 卡次不符 | 该班应有 4 个卡点,实际只有 2 个 | 槽位没配齐,工时必然偏小 |
这四条用条件格式标红就够,不需要复杂公式。关键是先筛异常、再算工时:先看这一行该不该有这么多卡、卡是不是都在合理窗口内,再套 MOD。顺序反了,月底你会在几百行"看着都合理"的数字里找不到错在哪。
最后一句口径:工时算出来只代表"上了多久",扣款与加班费怎么折算,要看公司依法制定的制度和当地人社口径,公式不负责这个判断。Excel 能做的是把每一分钟还原准确。
