excel加班时间 怎么算的

又到月底了，对吧？我知道。看着那张从打卡机里导出来，乱得像一锅粥的考勤表，脑袋里“嗡”的一声。尤其是老板或者HR甩过来一句：“小王，把这个月大家的加班时间算一下，半小时内给我。”

半小时？

呵呵。

他们以为就是简单加加减减，我们心里清楚，这里面的坑，比马里亚纳海沟还深。今天，我就不跟你扯那些教科书式的教程了，咱就聊点掏心窝子的，讲讲在Excel里算加班这件破事儿，到底是怎么从入门到“放弃”再到“卧槽原来如此”的。

一、观念先行：Excel里的时间，它根本就不是“人”看的时间！

你遇到的第一个坎，我敢打赌，绝对是这个：单元格里输入了上班时间9:00，下班时间21:00，然后你在另一个单元格里兴冲冲地输入 =B2-A2，回车，欸？结果怎么是12:00？你想要的是数字12啊！

或者更惨的，你直接拿这个12:00去乘以加班费单价，结果出来个莫名其妙的小数，当场懵圈。

记住我说的第一句真理：在Excel的宇宙里，时间格式本质上就是个小数。

1代表一整天，也就是24小时。那么中午12点，就是0.5；下午6点（18:0f0），就是0.75。你看到的9:00、21:00，不过是Excel为了让你看得懂，给你戴上的一层“格式”面具。

所以，21:00 - 9:00 的结果是12:00，它的“裸体”其实是0.5。你想把它变成我们能理解的12个小时，怎么办？

乘以24。

对，就是这么简单粗暴。=(B2-A2)*24，这个公式，是你计算工时的所有魔法的起点。请把它刻在DNA里。以后再碰到时间计算，先问问自己：我把它变回“人话”了吗？

二、基础建设：一张“不会说谎”的考勤底表

公式是武器，但没有好的战场，给你一把AK47也白搭。你的战场，就是那张考勤数据源表。在开始任何计算之前，请把你的表格整理成至少包含以下几列的“标准结构”：

1. 姓名
2. 日期
3. 上班打卡时间
4. 下班打卡时间

别嫌麻烦，磨刀不误砍柴工。那种把一个人一个月所有打卡时间都挤在一行的表格，看着整齐，算起来能让你想死的心都有。数据就要一是一，二是二，一行只记录一件事：某人某天的一次考勤。

确保你的时间格式是统一的，别一会儿是2023/10/27 9:00，一会儿是9:00 AM。选中所有时间列，右键，“设置单元格格式”，在“自定义”里，输入yyyy/m/d h:mm，或者就简单粗暴的h:mm，让它们长得一模一样。

三、核心战役：IF函数，加班规则的“翻译官”

好了，现在我们知道总工时是(下班打卡 - 上班打卡) * 24了。但加班不是这么算的。公司规定，每天工作满8小时之后才算加班，中午还得扣掉1小时吃饭时间呢！

这时候，逻辑判断的王者——IF函数就该登场了。

它的语法很简单：IF(逻辑判断, 判断为真时干什么, 判断为假时干什么)。

我们来翻译一下公司的加班规则：

• 规则：“如果当天的总工时（下班时间减去上班时间）大于9个小时（8小时工作+1小时午休），那么多出来的部分才算加班；否则，加班就是0。”

翻译成IF函数语言就是：

=IF((B2-A2)*24 > 9, (B2-A2)*24 - 9, 0)

我们来拆解一下：

• (B2-A2)*24 > 9：这就是逻辑判断。当天的总时长是不是超过了9小时？
• (B2-A2)*24 - 9：如果超过了，那真的加班时长就是总时长减去这9个小时。
• 0：如果没超过，那加班时长就是假的，也就是0。

这个公式一拖，一整列的日常加班时间就出来了。是不是感觉有点内味儿了？

四、地狱模式：要了命的跨天加班

你以为这就完了？太天真了。程序员小张，昨晚发版本，加班到了凌晨2点。他的打卡记录是：上班9:00，下班2:00。

你开开心心地套上公式 =(B2-A2)*24…欸？结果怎么是个负数？Excel直接给你报错#######。

这就是跨天加班，Excel计算时间的头号杀手。在它眼里，2:00（第二天）就是比9:00（第一天）小，一减可不就是负的么。

怎么破？

方法一：手动调整。看见负数，就手动改成正的。——别，求你了，数据一多，你改到明年也改不完，还容易出错。

方法二：用IF函数再做一次判断。

逻辑是这样的：“如果下班时间比上班时间还早，那说明是跨天了，计算的时候就得给下班时间加上一整天（也就是数字1）；否则，就是正常计算。”

公式就变成了这样：

=IF(B2 < A2, (B2 - A2 + 1) * 24, (B2 - A2) * 24)

• B2 < A2：判断下班时间是不是小于上班时间。
• B2 - A2 + 1：如果是，就给这个时间差加上1（一整天），负数瞬间就变成了正确的正数。
• 剩下的你都懂了。

这个公式，能帮你摆平90%的跨天加班问题。它不优雅，但极其有效，像个干脏活累活的壮汉。

五、王者进阶：周末、节假日，不同的加班倍率怎么算？

“小王啊，周末加班是2倍工资，法定节假日是3倍，你再给分开算算。”

老板一句话，你的工作量翻了三倍。

这时候，单靠IF已经不够用了，我们需要引入新的“神兵利器”——WEEKDAY函数和一张“假期日历表”。

首先，在你的考勤表旁边，新建一列，叫“日期类型”。

然后，我们用WEEKDAY函数来判断当天是周几。=WEEKDAY(A2, 2)。

• A2是日期那一列。
• 后面的参数2是个小技巧，它代表把周一作为一周的第一天，这样返回的1-5就是工作日，6和7就是周末。非常符合中国人的习惯。

有了周几，我们就可以用一个嵌套的IF来判断了：

=IF(WEEKDAY(A2, 2) > 5, "周末", "工作日")

意思是，如果WEEKDAY函数返回的数字大于5（也就是周六或周日），那么就在单元格里显示“周末”，否则就显示“工作日”。

那法定节假日呢？这玩意儿没规律，只能“死记硬背”。

1. 单独建一个sheet，命名为“节假日”，在A列里把你公司今年所有的法定节假日日期都列出来。
2. 回到考勤表，我们需要用一个“查水表”的函数，比如VLOOKUP或者更牛的XLOOKUP。这里用个简单易懂的COUNTIF也行。

我们来更新一下“日期类型”那一列的公式，让它变得更智能：

=IF(COUNTIF(节假日!A:A, A2) > 0, "法定假日", IF(WEEKDAY(A2, 2) > 5, "周末", "工作日"))

这个公式有点长，别怕，我们把它当成俄罗斯套娃来拆：

• 最里面的IF(WEEKDAY(A2, 2) > 5, "周末", "工作日")，我们已经认识了。
• 外层的COUNTIF(节假日!A:A, A2)是去“节假日”那张表里数一数，今天的日期（A2）在不在那个列表里。如果在，COUNTIF的结果就大于0。
• 所以整个公式的意思是：先查查今天是不是法定假日，如果是，就标记“法定假日”；如果不是，再查查是不是周末，如果是，就标记“周末”；如果还不是，那就是“工作日”了。

现在，你的表格里有了一列清晰的“日期类型”，再结合我们之前算的加班时长，是不是感觉天下尽在掌握了？

六、终极汇总：SUMIFS和数据透视表，让结果一目了然

你把每天的加班都算出来了，但老板要的是：“张三，10月份，总共加了多少小时班？其中周末加班多少，节假日加班多少？”

这时候，你需要的是汇总工具。

方法一：SUMIFS函数，精确制导

SUMIFS是个多条件求和的函数，你想怎么筛，它就怎么筛。

• 计算张三所有加班总时长： =SUMIFS(加班时长列, 姓名列, "张三")

• 计算张三周末的加班时长： =SUMIFS(加班时长列, 姓名列, "张三", 日期类型列, "周末")

• 计算张三法定节假日的加班时长： =SUMIFS(加班时长列, 姓名列, "张三", 日期类型列, "法定假日")

是不是指哪儿打哪儿？

方法二：数据透视表（PivotTable），一键生成，降维打击

如果你的数据量很大，或者老板的需求千奇百怪，一会要看部门的，一会要看项目的，用SUMIFS会把你写到手抽筋。

这时候，请出Excel的最终大杀器——数据透-视表。

1. 选中你所有的考勤数据。
2. 点击菜单栏的“插入” -> “数据透视表”。
3. 在右侧的字段列表里，开始“拖拖拽拽”：
   • 把“姓名”拖到“行”区域。
   • 把“日期类型”拖到“列”区域。
   • 把“加班时长”拖到“值”区域。

砰！

一张完美的加班汇总表瞬间就出现在你眼前。每个人，在工作日、周末、法定节假日分别加了多少班，一清二楚，连合计都给你算好了。

这感觉，就像你吭哧吭哧用斧头砍了半天树，突然有人递给你一把电锯。

计算加班时间，从来都不是一个简单的数学问题，它是一个逻辑问题，一个流程问题。从最基础的对“时间”的理解，到搭建清晰的数据结构，再到用IF翻译复杂的规则，用WEEKDAY和VLOOKUP应对特殊情况，最后用SUMIFS或数据透视表华丽收尾。

这整个过程，就像在打一场仗。而你，就是那个运筹帷幄的将军。下一次，当有人再让你半小时内算出加班时，你可以微微一笑，从容地打开你的Excel，开始你的表演。