ARTICLE · 1381230
Excel 跨凌晨工时算出负数,MOD 一行修好
讲公式最怕只说"用 B2 减 A2"——B2 里到底是几点、A2 是不是带日期,读者只能猜。所以下面先把一张三行的工作表摊开,把 B3 是 2026-03-10 20:00、C3 是 2026-03-11 08:00 这样写死,再把公式一格一格代进去看结果。
一、先看这张表:B2、C2 里到底是什么
假设你的原始打卡表长这样,A 列姓名、B 列上班卡、C 列下班卡,第 1 行是表头,第 2 到 4 行是三个人:
三行看起来像同一件事,其实有两种数据形态:张三和李四是完整日期时间(带 2026-03-11 这个日期),王五是纯时分(只有 20:00 和 08:00,没有日期)。这个差别就是后面所有问题的根源,后面每个公式我都会指明它在哪一行、引用哪两格。
怎么判断自己表里是哪一种:选中 B 列某一格,把格式改成"常规"。显示成 46091.833333 这种带整数部分的是完整日期时间;显示成 0.833333(小于 1)的是纯时分。
二、负数从哪来:时间在 Excel 里是小数
Excel 把一整天当成 1,时间就是这一天里的小数。所以 09:00 是 0.375、20:00 是 0.833333、次日 08:00 是 1.333333(如果带日期)或 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)
再换王五那行(D4),相减得 −0.5,MOD(−0.5, 1) 得到 0.5,结果还是 12。负数为什么自己变正?因为 Excel 的 MOD 结果符号跟随除数,而不是被除数;除数是 1,结果就永远落在 0 到 1 之间。
三行一起套上,左边是直接相减、右边是 MOD,差别一目了然:
| 9 | ||||
| 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):
| 11.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,就得配几条上限校验。
这四条用条件格式标红就够,不需要复杂公式。关键是先筛异常、再算工时:先看这一行该不该有这么多卡、卡是不是都在合理窗口内,再套 MOD。顺序反了,月底你会在几百行"看着都合理"的数字里找不到错在哪。
最后一句口径:工时算出来只代表"上了多久",扣款与加班费怎么折算,要看公司依法制定的制度和当地人社口径,公式不负责这个判断。Excel 能做的是把每一分钟还原准确。
本文由树懒办公整理 —— 把考勤机导出的原始打卡,变成一份能直接交付的考勤报表。支持多班次并存、跨凌晨打卡自动归属、4 次/6 次打卡槽位、请假半天分配。















































































