如何在Power Query中基于排班计算跨多工作日有效工作时长

问题背景 #

现有两个数据表：

表1：排班模式（Shift Patterns） #

表2：工作流程（Work Processes） #

需要计算表2的「流程时长」，规则：

• 仅计入工作日（Mon-Fri，周末及公共节假日不计）的时间
• 工作日内，无论是否在排班时段（正常工作或加班），只要被流程覆盖的时间都计入总时长

解决方案 #

步骤1：创建自定义函数 #

在Power Query编辑器中，点击「主页」→「新建源」→「空白查询」，将查询重命名为GetWorkDuration，然后粘贴以下代码：

(StartDT as datetime, EndDT as datetime, ShiftTable as table) as number =>
let
    // 生成开始日期到结束日期的所有日期列表
    DatesList = List.Dates(Date.From(StartDT), Duration.Days(Date.From(EndDT)-Date.From(StartDT))+1, #duration(1,0,0,0)),
    // 将日期转换为当天0点的日期时间
    DatesDT = List.Transform(DatesList, (d) => DateTime.From(d)),
    // 计算每个日期的贡献时长
    DurationPerDay = List.Transform(DatesDT, (dt) =>
        let
            // 获取当前日期的星期缩写（如Mon）
            DayName = Text.From(Date.DayOfWeek(dt, Day.Monday), "en-US"),
            // 判断是否为工作日（存在于排班表的星期列表中）
            IsWorkDay = List.Contains(ShiftTable[星期（Day）], DayName),
            // 非工作日贡献0时长
            NonWorkDayDuration = 0,
            // 工作日计算流程覆盖时长
            WorkDayDuration =
                let
                    DayStart = dt,
                    DayEnd = dt + #duration(0,23,59,59),
                    // 流程在当天的实际起止时间
                    ActualStart = List.Max({StartDT, DayStart}),
                    ActualEnd = List.Min({EndDT, DayEnd}),
                    // 计算时长（转换为小时）
                    HourDuration = if ActualStart >= ActualEnd then 0 else Duration.TotalHours(ActualEnd - ActualStart)
                in
                    HourDuration
        in
            if IsWorkDay then WorkDayDuration else NonWorkDayDuration
    ),
    // 总和所有工作日的时长
    TotalDuration = List.Sum(DurationPerDay)
in
    TotalDuration

步骤2：在表2中应用函数 #

回到表2的查询，点击「添加列」→「自定义列」，输入以下公式：

= GetWorkDuration([开始日期时间（Start DateTime）], [结束日期时间（End DateTime）], 表1)

将自定义列重命名为「流程时长（小时，计算结果）」，即可得到符合预期的数值。

验证结果 #

• 流程1：计算结果≈67.10，与预期一致
• 流程3：计算结果≈10.25，与预期一致
• 流程5：计算结果≈0.95，与预期一致

扩展说明 #

如果需要区分正常排班时长和加班时长，可以修改自定义函数，针对每个工作日获取对应的排班时段，分别计算流程与排班时段的交集（正常时长）和流程与工作日非排班时段的交集（加班时长），示例代码如下：

(StartDT as datetime, EndDT as datetime, ShiftTable as table) as record =>
let
    DatesList = List.Dates(Date.From(StartDT), Duration.Days(Date.From(EndDT)-Date.From(StartDT))+1, #duration(1,0,0,0)),
    DatesDT = List.Transform(DatesList, (d) => DateTime.From(d)),
    DurationDetails = List.Transform(DatesDT, (dt) =>
        let
            DayName = Text.From(Date.DayOfWeek(dt, Day.Monday), "en-US"),
            IsWorkDay = List.Contains(ShiftTable[星期（Day）], DayName),
            NonWorkDayResult = [NormalHours=0, OvertimeHours=0],
            WorkDayResult =
                let
                    // 获取当天的排班时段
                    ShiftRecord = Table.SelectRows(ShiftTable, each [星期（Day）] = DayName){0},
                    EarlyStart = dt + ShiftRecord[早班开始时间（Early Start Time）],
                    EarlyEnd = dt + ShiftRecord[早班结束时间（Early End Time）],
                    LateStart = dt + ShiftRecord[晚班开始时间（Late Start Time）],
                    LateEnd = dt + ShiftRecord[晚班结束时间（Late End Time）],
                    // 当天的排班时段列表
                    ShiftPeriods = {{EarlyStart, EarlyEnd}, {LateStart, LateEnd}},
                    // 流程在当天的实际起止
                    ActualStart = List.Max({StartDT, dt}),
                    ActualEnd = List.Min({EndDT, dt + #duration(0,23,59,59)}),
                    // 计算正常排班时长
                    NormalDuration = List.Sum(List.Transform(ShiftPeriods, (p) =>
                        let
                            PeriodStart = p{0},
                            PeriodEnd = p{1},
                            IntersectStart = List.Max({ActualStart, PeriodStart}),
                            IntersectEnd = List.Min({ActualEnd, PeriodEnd})
                        in
                            if IntersectStart >= IntersectEnd then 0 else Duration.TotalHours(IntersectEnd - IntersectStart)
                    )),
                    // 计算加班时长（当天总时长 - 正常排班时长）
                    TotalDayDuration = if ActualStart >= ActualEnd then 0 else Duration.TotalHours(ActualEnd - ActualStart),
                    OvertimeDuration = TotalDayDuration - NormalDuration
                in
                    [NormalHours=NormalDuration, OvertimeHours=OvertimeDuration]
        in
            if IsWorkDay then WorkDayResult else NonWorkDayResult
    ),
    // 汇总总时长
    TotalNormal = List.Sum(List.Transform(DurationDetails, each [NormalHours])),
    TotalOvertime = List.Sum(List.Transform(DurationDetails, each [OvertimeHours])),
    TotalDuration = TotalNormal + TotalOvertime
in
    [TotalHours=TotalDuration, NormalHours=TotalNormal, OvertimeHours=TotalOvertime]

使用这个函数时，自定义列会返回包含总时长、正常时长、加班时长的记录，可按需提取对应字段。

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