树懒办公 logo树懒办公考勤统计

Excel 里跨凌晨的工时算出负数,用 MOD 一行修好

讲公式最怕只说"用 B2 减 A2"——B2 里到底是几点、A2 是不是带日期,读者只能猜。所以下面先把一张三行的工作表摊开,把 B3 是 2026-03-10 20:00、C3 是 2026-03-11 08:00 这样写死,再把公式一格一格代进去看结果。

树懒办公 · 考勤百科  更新于 2026-11-16  阅读约 8 分钟

一、先看这张表:B2、C2 里到底是什么

假设你的原始打卡表长这样,A 列姓名、B 列上班卡、C 列下班卡,第 1 行是表头,第 2 到 4 行是三个人:

行A:姓名B:上班卡C:下班卡
1姓名上班卡下班卡
2张三2026-03-10 09:002026-03-10 18:00
3李四2026-03-10 20:002026-03-11 08:00
4王五20:0008:00

三行看起来像同一件事,其实有两种数据形态:张三和李四是完整日期时间(带 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(如果不带)。

单元格显示内容实际存储值拆开看
B32026-03-10 20:0046091.833333日期 46091 + 时间 0.833333
C32026-03-11 08:0046092.333333日期 46092 + 时间 0.333333
B420:000.833333只有时间,没有日期
C408:000.333333只有时间,没有日期

于是两行的差别就出来了:

所以负数的成因不是"夜班特殊",而是这一行的两个时间都没有日期,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 − B30.5
② 取一天内的余数MOD(0.5, 1)0.5
③ 换成小时0.5 × 2412
④ 保留两位ROUND(12, 2)12

再换王五那行(D4),相减得 −0.5,MOD(−0.5, 1) 得到 0.5,结果还是 12。负数为什么自己变正?因为 Excel 的 MOD 结果符号跟随除数,而不是被除数;除数是 1,结果就永远落在 0 到 1 之间。

三行一起套上,左边是直接相减、右边是 MOD,差别一目了然:

行姓名C − B直接相减 ×24MOD(C−B,1) ×24
2张三0.37599
3李四0.51212
4王五−0.5−1212

看清楚这张表的意义: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 有效工时
320:0008:001200.511.5
508:0000:001610.514.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 能做的是把每一分钟还原准确。

相关阅读

跨凌晨的班,不用再自己修公式

班次配好后,凌晨的打卡自动归到上班当天,工时与休息扣除一次算准。

免费开始使用