KutoolsforOffice — 一套方案，五大工具。事半功倍。

如何在 Excel 中快速计算加班时长和加班工资？

作者孙修改日期

在众多工作场所，准确追踪员工工时（尤其是加班时间）对薪酬计算和合规性至关重要。假设您有一张表格，记录了员工的上班打卡时间、午休时段及下班打卡时间，并希望快速计算出每日的加班时长及其对应的加班工资（如下图所示）。高效精准的计算不仅能节省时间，还能显著降低人工错误的风险——这一点在汇总多名员工或多期薪酬数据时尤为关键。
计算加班时间和工资

计算加班时长与工资

使用 VBA 宏批量计算加班/工资

使用数据透视表进行汇总分析

蓝色右向箭头气泡计算加班时长与工资

您可以借助 Excel 内置公式，高效计算加班时长及对应的工资。该方法适用于单个员工记录或小型数据集的简单计算。以下是详细操作步骤：

1. 首先，计算每日标准工作时间。单击单元格 F2，然后输入以下公式：

=IF((((C2-B2)+(E2-D2))*24)>8,8,((C2-B2)+(E2-D2))*24)

按 Enter 键后，向下拖动自动填充柄，即可将公式复制到其他行，从而在 F 列显示每日的标准工作时间。
应用公式显示正常工作时间

2. 接下来，计算加班时长。在单元格 G2 中输入以下公式：

=IF(((C2-B2)+(E2-D2))*24>8, ((C2-B2)+(E2-D2))*24-8,0)

按 Enter 键后，向下拖动公式以填充加班列的所有行，每日的加班时长将显示在 G 列中。
应用公式计算加班时间

上述公式中：

  • B2：上班开始时间（打卡时间）
  • C2：午休开始时间
  • D2：午休结束时间
  • E2：下班时间（打卡结束时间）
  • 该计算基于标准工作日为 8 小时的假设；您可根据公司政策，按需调整公式中的“8”及相关时间引用。
提示：请确保时间值在 Excel 中采用正确的格式（例如 hh:mm）。

3. 要汇总一周的总标准工时和总加班时长，请选中单元格 F8 并输入：

=SUM(F2:F7)

然后将此公式拖至单元格 G8，即可自动计算出总加班时长。
应用公式获取正常工时和加班工时总和

4. 在指定单元格中计算标准工时与加班工资。例如，若要在单元格 F9 中计算标准工资，请输入：

=F8*I2

同理，在单元格 G9 中计算加班工资，请输入：

=G8*J2

此处，I2 和 J2 应分别填入标准工时和加班工时对应的每小时费率。
使用公式计算正常工时和加班工资

要计算标准工资与加班工资的总额，请在单元格 H9 中使用简单的求和公式：

=F9+G9

该最终结果代表所审期间的总薪酬，包括标准工资及额外加班工资。
应用公式计算工资总额

这种基于公式的计算方法简单快捷，非常适合日常或每周的计算需求，并且在工作安排或加班标准发生变化时也能轻松调整。然而，若涉及大量员工或需要更高级的报表功能，则其他 Excel 功能或自动化方案可能更为高效。

  • 优势：简单易用，无需编程知识，适用于小型数据集，轻松维护。
  • 局限性：需为每位员工或每张表格手动设置，若表格结构变更还需维护公式，且不适用于超大型数据集。

如果您的数据量不断增长，或需要为多名员工、不同周期批量计算加班费或工资，建议采用自动化流程，或充分利用 Excel 内置的分析工具。请参考以下选项：

蓝色右向箭头气泡 使用 VBA 宏批量计算加班/工资

处理包含多名员工、多个工作表或不同时期的大型数据集时，手动填充公式效率低下——此时可借助 VBA 宏自动完成全部计算，轻松简化重复性操作，尤其适用于复杂数据结构或需频繁导入数据的场景。

场景：您有一张包含员工姓名、上班开始时间、午休开始时间、午休结束时间及下班时间的表格，希望批量计算标准工时、加班时长和工资。

注意：运行前请务必保存工作簿，并确保已启用宏。首次运行或测试时，建议提前备份，以防意外数据丢失！

1. 单击开发工具 > Visual Basic。在 Microsoft Visual Basic for Applications 窗口中，单击插入 > 模块，然后将以下代码复制并粘贴到模块中：

Sub BatchOvertimeCalculation()
    Dim ws As Worksheet
    Dim i As Long
    Dim lastRow As Long
    Dim regHourCol As String, overtimeCol As String, payCol As String
    Dim startCol As String, lunchStartCol As String, lunchEndCol As String, endCol As String
    Dim regHourlyRate As Double, overtimeHourlyRate As Double
    
    On Error Resume Next
    
    regHourCol = InputBox("Enter column letter for Regular Hour (output):", "KutoolsforExcel", "F")
    overtimeCol = InputBox("Enter column letter for Overtime (output):", "KutoolsforExcel", "G")
    payCol = InputBox("Enter column letter for Payment (output):", "KutoolsforExcel", "H")
    startCol = InputBox("Enter column letter for Work Start:", "KutoolsforExcel", "B")
    lunchStartCol = InputBox("Enter column letter for Lunch Start:", "KutoolsforExcel", "C")
    lunchEndCol = InputBox("Enter column letter for Lunch End:", "KutoolsforExcel", "D")
    endCol = InputBox("Enter column letter for Work End:", "KutoolsforExcel", "E")
    
    regHourlyRate = Application.InputBox("Enter hourly rate for regular hours:", "KutoolsforExcel", 15, Type:=1)
    overtimeHourlyRate = Application.InputBox("Enter hourly rate for overtime:", "KutoolsforExcel", 22.5, Type:=1)
    
    Set ws = Application.ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, startCol).End(xlUp).Row
    
    For i = 2 To lastRow
        Dim totalHours As Double, regHours As Double, overtimeHours As Double
        
        totalHours = ((ws.Range(lunchStartCol & i) - ws.Range(startCol & i)) + _
                      (ws.Range(endCol & i) - ws.Range(lunchEndCol & i))) * 24
        
        If totalHours > 8 Then
            regHours = 8
            overtimeHours = totalHours - 8
        Else
            regHours = totalHours
            overtimeHours = 0
        End If
        
        ws.Range(regHourCol & i).Value = regHours
        ws.Range(overtimeCol & i).Value = overtimeHours
        ws.Range(payCol & i).Value = regHours * regHourlyRate + overtimeHours * overtimeHourlyRate
    Next i
    
    MsgBox "Batch calculation complete!", vbInformation, "KutoolsforExcel"
End Sub

2. 输入代码后，单击 VBA 工具栏中的运行按钮按钮运行宏。在弹出的对话框中输入所需信息（例如时间数据和工资率所在的列），宏将自动为每行填充标准工时、加班时长和总工资列。
故障排除：请确保所有时间列均采用正确的 Excel 时间格式。若任一单元格包含无效或空数据，宏将跳过该行或返回“0”。运行宏后，请手动核对若干行以确保准确性。

  • 优势：处理大型或复杂数据集极为高效，轻松避免手动复制和拖拽公式。
  • 局限性：需具备一定的 VBA 基础，启用宏时会弹出安全警告，且需谨慎确保引用正确的列。

总结建议：对于日常或一次性计算，公式法快速直观；当加班计算任务扩展至更多记录，或报表需求日益复杂时，使用 VBA 自动化可显著减少人工操作与出错风险。请务必再次确认时间格式正确，并确保所选方案的计算逻辑符合公司加班政策。如遇错误（如 #VALUE！），请检查单元格格式或是否存在空白条目。执行批量操作前，建议先保留备份，以防万一。

轻松在 Excel 中为日期添加天数、年份、月份、小时、分钟和秒数

如果单元格中包含日期，而您需要为其添加天数、月份、年份、小时、分钟或秒数，使用公式往往既复杂又难以记忆。借助 Kutools for Excel 的日期时间助手工具，您无需记忆任何复杂公式，即可轻松为日期添加时间段、计算日期差值，甚至根据出生日期快速确定年龄！

Kutools for Excel——通过 300 多个必备工具大幅提升 Excel 能力，让您的工作更快、更轻松，并借助 AI 功能实现更智能的数据处理与高效办公！立即获取

最佳办公效率工具

🤖KUTOOLS AI 助手：基于以下内容革新数据分析：智能执行   |  生成代码|  创建自定义公式  |  数据分析及生成图表|  调用 Kutools Functions……
热门功能：查找、高亮或标记重复项   |  删除空白行   |  合并列或单元格且不丢失数据   |  不使用公式的四舍五入……
高级 LOOKUP：多条件 VLookup  |  多值 VLookup  |   跨多工作表 VLookup   |   模糊查找……
高级下拉列表：快速创建下拉列表   |  级联下拉列表   |  多选下拉列表……
列管理器：添加指定数量的列|移动列|切换隐藏列的可见性状态|比较区域与列……
特色功能：网格聚焦   |  设计视图   |增强编辑栏   | 工作簿和表管理器   |  资源库（自动文本）|  日期提取   |  汇总工作表  |  加密/解密单元格   | 按列表发送邮件   |  超级筛选   |   特殊筛选（筛选粗体单元格/斜体/删除线……） ......
精选 15 工具集：12 文本工具（添加文本，删除特定字符，……）|   50+ 图表 类型（甘特图，……）|   40+ 实用公式（基于生日计算年龄，……）|   19 插入工具（插入二维码，从路径插入图片，……）|   12 转换工具（小写金额转大写，汇率转换，……）|   7 合并和拆分工具（高级合并行，分割单元格，……）|……更多
在您的首选语言中使用 Kutools – 支持英语、西班牙语、德语、法语、中文及 40+ 种其他语言！

使用 Kutools for Excel 大幅提升您的 Excel 技能，体验前所未有的高效。Kutools for Excel 提供 300 多项高级功能，助您提升生产力、节省时间。立即点击此处，获取您最需要的功能……

Office Tab 为 Office 带来标签式界面，让您的工作更轻松

  • 在 Word、Excel、PowerPoint、Publisher、Access、Visio 和 Project 中启用标签式编辑和阅读。
  • 在同一个窗口的新标签页中打开并创建多个文档，而非在新窗口中。
  • 将您的工作效率提升 50%，每天减少数百次鼠标点击！

所有 Kutools 插件，一个安装程序

Kutools for Office 套件捆绑了适用于 Excel、Word、Outlook 和 PowerPoint 的插件以及 Office Tab Pro，非常适合需要跨多个 Office 应用高效协作的团队。

ExcelWordOutlookTabsPowerPoint
  • 一体化套件— Excel、Word、Outlook 和 PowerPoint 插件 + Office Tab Pro
  • 一个安装程序，一个许可证— 几分钟内完成设置（支持 MSI）
  • 协同效果更佳— 在多个 Office 应用中实现高效协同
  • 30 天全功能试用— 无需注册，无需信用卡
  • 超值之选— 比单独购买插件更省钱