在 Excel 中计算考勤，核心在于处理日期与时间的差值，并排除非工作日（周末/节假日）。
以下是从基础到进阶的几种常用方法,你可以根据实际数据格式选择最适合的一种。
基础场景：计算工作时长（小时数）
假设数据如下：
- A列：日期
- B列：上班时间
- C列：下班时间
简单计算（不含午休扣除）
如果不需要扣除午休,直接用下班时间减去上班时间：
=(C2-B2)24
- 注意：
- 乘以
24是因为 Excel 中时间是以“天”为单位存储的，乘以24转换为小时。 - 单元格格式需设置为数值或常规，不要设置为时间格式，否则结果会显示为时间（如 08:30 而不是 8.5）。
- 乘以
处理跨天打卡（如夜班）
如果员工可能跨天打卡（22:00 上班，次日 06:00 下班），上述公式会得出负数，使用 IF 函数修正：
=IF(C2<B2, C2-B2+1, C2-B2)24
- 逻辑：如果下班时间 < 上班时间，说明跨天了，加
1（即加1天）再相减。
扣除午休时间
假设午休固定为 1 小时（12:00-13:00），且假设所有打卡都在午休前后：
=MAX(0, (C2-B2)24 - 1)
- 逻辑：总时长减去1小时，使用
MAX(0, ...)防止因打卡异常导致出现负数时长。
进阶场景：计算“工作日天数”（排除周末）
如果你需要计算从入职到现在的实际出勤天数（不含周六日），使用 NETWORKDAYS 函数。
公式：
=NETWORKDAYS(开始日期, 结束日期)
示例：
- A2：2026/10/01
- B2：2026/10/10
- 公式：
=NETWORKDAYS(A2, B2) - 结果：8天（自动排除了中间的周六、周日）
进阶：扣除法定节假日
如果公司需要扣除特定的法定节假日（如春节、国庆等），可以将假期列表放在一个区域（H2:H10），然后使用第三个参数：
=NETWORKDAYS(A2, B2, H2:H10)
- H2:H10 是你手动输入的法定节假日日期列表。
综合场景：考勤汇总表（自动统计迟到、早退、缺勤）
这是最实用的场景,假设：
- B列：应上班时间（如 09:00）
- C列：应下班时间（如 18:00）
- D列：实际上班时间
- E列：实际下班时间
- F列：应出勤天数（可用 NETWORKDAYS 计算）
判断是否迟到
=IF(D2="","", IF(D2>B2, "迟到", "正常"))
- 更精确的迟到分钟数：
=IF(D2>B2, (D2-B2)2460, 0)
- 结果单位为分钟。
判断是否早退
=IF(E2<C2, "早退", "正常")
- 早退分钟数：
=IF(E2<C2, (C2-E2)2460, 0)
计算实际出勤小时数（含迟到/早退影响）
=IF(AND(D2<>"", E2<>""), MAX(0, (E2-D2)24 - 1), 0)
- 这里假设扣除1小时午休,如果某天没打卡（D2或E2为空），则返回0。
统计月度迟到次数
假设 G 列是“迟到状态”（值为“迟到”或空）：
=COUNTIF(G2:G31, "迟到")
统计月度迟到总分钟数
假设 H 列是“迟到分钟数”：
=SUM(H2:H31)
高级技巧：使用条件格式高亮异常
为了让考勤表更直观,可以使用条件格式自动标红异常数据：
-
标红迟到：
- 选中实际上班时间列（D列）。
- 开始 > 条件格式 > 新建规则 > 使用公式。
- 输入：
=D2>B2 - 设置格式为红色字体或填充红色背景。
-
标红早退：
- 选中实际下班时间列（E列）。
-
公式：
=E2<C2 - 设置格式为黄色填充。
标红未打卡：
- 公式：
=OR(D2="", E2="") - 设置格式为灰色填充。
- 公式：
常见错误排查
-
结果显示为小数或时间格式不对：
- 确保计算时长的单元格格式设置为“常规”或“数值”，而不是“时间”。
- 时间相减后乘以
24得到小时，乘以2460得到分钟。
-
NETWORKDAYS 结果不对：
- 检查日期列是否为真正的“日期格式”，而不是文本，可以用
=ISTEXT(A2)测试，如果返回 TRUE 则需转换。 - 确保节假日列表中的日期也是日期格式。
- 检查日期列是否为真正的“日期格式”，而不是文本，可以用
-
跨天打卡计算错误：
- 务必使用
IF(C2<B2, C2-B2+1, C2-B2)这种逻辑，否则深夜下班会算成负数。
- 务必使用
总结建议
- 简单考勤：用
(下班-上班)24。 - 排除周末：用
NETWORKDAYS(开始, 结束, 节假日)。 - 复杂考勤：结合
IF、MAX、COUNTIF和条件格式，建立自动化模板。
如果你能提供具体的 Excel 截图或数据结构（是否有午休、是否跨天、是否需要扣款），我可以为你定制更精确的公式。
首发原创文章，作者：世雄 - 原生数据库架构专家，如若转载，请注明出处：https://idctop.com/article/486084.html
