如何根据任务间隙时长规则自动计算工时表应付薪酬

核心计算逻辑 #

计薪总时长由两部分组成：

• 所有有效任务本身的时长总和
• 相邻任务间隙中≤30分钟的部分总和，超过30分钟的间隙不计入

前置数据准备 #

1. 按「员工ID+任务日期」分组，将同一分组下的所有任务按任务开始时间升序排序，这是间隙计算准确的前提
2. 统一数据表列格式：确保任务开始时间、结束时间为时间格式而非文本格式

公式实现（兼容Excel 365/谷歌表格） #

假设你的数据表列如下：

• A列：员工ID
• B列：任务日期
• C列：单任务开始时间
• D列：单任务结束时间

方法1：使用辅助列（易理解，适合新手） #

1. 单任务时长（E列）：E2单元格输入公式，下拉填充
   =(D2-C2)*24*60
   计算结果为单任务的分钟数，需要小时的话去掉*60即可。

2. 有效间隙时长（F列）：F3单元格输入公式，下拉填充（F2为当日第一个任务，无上一个任务，直接填0）
   =IF(AND(A3=A2,B3=B2),MAX(0,MIN(30,(C3-D2)*24*60)),0)
   公式逻辑：如果当前行和上一行是同一员工同一日期的任务，计算间隙时长，最多计入30分钟，否则间隙为0。

3. 当日总计薪时长（G列）：G2单元格输入公式，下拉填充
   =SUMIFS(E:E,A:A,A2,B:B,B2)+SUMIFS(F:F,A:A,A2,B:B,B2)
   直接汇总同一员工同一天的所有任务时长和有效间隙时长，得到最终计薪时长。

方法2：无辅助列一步计算（适合高阶用户） #

如果使用支持LAMBDA函数的版本，可以直接在G列输入数组公式，无需新增辅助列：

=BYROW(A2:A,LAMBDA(x,IF(x="",,
SUM(FILTER((D:D-C:C)*24*60,A:A=x,B:B=INDEX(B:B,ROW(x)))) +
SUM(FILTER(MAP(ROW(C:C),LAMBDA(y,IF(AND(INDEX(A:A,y)=x,INDEX(B:B,y)=INDEX(B:B,ROW(x)),y>2),MAX(0,MIN(30,(INDEX(C:C,y)-INDEX(D:D,y-1))*24*60)),0)),A:A=x,B:B=INDEX(B:B,ROW(x))))
)))

注意事项 #

• 若存在跨零点的任务，建议提前拆分为当日和次日两部分再参与计算
• 可以搭配数据透视表直接按员工、周/月维度汇总计薪时长，实现全流程自动化

内容的提问来源于stack exchange，提问作者Kris12