Excel Power Query中仅周二计算4个工作日出错的问题排查

本人是Excel和Power Query新手，正在自动化工作中的重复任务。现有订单数据及DispatchDate（发货日期）列，需添加“Out of Delay Date”列，计算**包含发货日在内的4个工作日（不含周末、含节假日）**作为截止日期。该公式在多数日期下正常，但当DispatchDate为周二时，结果错误落在下周日，预期应为下周五；使用5个工作日的相同公式则运行正常，求排查问题原因。

原代码 #

AddBusinessDays = (startDate as date, numDays as number) as date => 
    let
        weekdayList = {1, 2, 3, 4, 5},
        daysAdded = List.Generate(() => [Count=0, Date=startDate], 
                                  each [Count] < numDays, 
                                  each let 
                                        nextDay = Date.AddDays([Date], 1),
                                        isWeekday = List.Contains(weekdayList, Date.DayOfWeek(nextDay, Day.Monday) + 1)
                                      in [Count=if isWeekday then [Count]+1 else [Count], 
                                          Date=nextDay]),
        lastDay = List.Last(daysAdded)[Date]
    in
        lastDay
in
    AddBusinessDays([DispatchDate], 5)),
    #"Lignes triées1" = Table.Sort(#"Personnalisée ajoutée",{{"Out of delay date", Order.Ascending}}),
    #"Type modifié2" = Table.TransformColumnTypes(#"Lignes triées1",{{"Out of delay date", type date}}),
    #"Personnalisée ajoutée1" = Table.AddColumn(#"Type modifié2", "Updates", each ""),
    #"Type modifié3" = Table.TransformColumnTypes(#"Personnalisée ajoutée1",{{"Updates", type date}})
in
    #"Type modifié3"

问题原因 #

核心错误在于计数逻辑未将起始日计入工作日统计：

• 原函数初始Count=0，且从起始日的下一天开始判断工作日并累加计数，这意味着起始日本身没有被算入numDays的范围内
• 当需求是「包含发货日在内的4个工作日」时，原逻辑实际计算的是起始日之后的4个工作日，而非包含起始日的4个，导致日期偏移

以周二为起始日的场景为例：原逻辑会从周二（Count=0）开始，依次遍历周三（Count=1）、周四（Count=2）、周五（Count=3），之后遇到周六、周日（非工作日，Count不变），直到下周一（Count=4）才停止循环，最终返回的日期远超预期。

修正后的代码 #

AddBusinessDays = (startDate as date, numDays as number) as date => 
    let
        weekdayList = {1, 2, 3, 4, 5}, // 周一到周五对应1-5
        // 起始日本身算第一个工作日，初始Count设为1
        daysAdded = List.Generate(() => [Count=1, Date=startDate], 
                                  each [Count] < numDays, // 计数未达目标时继续循环
                                  each let 
                                        nextDay = Date.AddDays([Date], 1),
                                        isWeekday = List.Contains(weekdayList, Date.DayOfWeek(nextDay, Day.Monday) + 1)
                                      in [Count=if isWeekday then [Count]+1 else [Count], 
                                          Date=nextDay]),
        // 处理numDays=1的特殊情况（无需循环）
        lastDay = if numDays = 1 then startDate else List.Last(daysAdded)[Date]
    in
        lastDay
in
    AddBusinessDays([DispatchDate], 4)), // 根据需求传入4
    #"Lignes triées1" = Table.Sort(#"Personnalisée ajoutée",{{"Out of delay date", Order.Ascending}}),
    #"Type modifié2" = Table.TransformColumnTypes(#"Lignes triées1",{{"Out of delay date", type date}}),
    #"Personnalisée ajoutée1" = Table.AddColumn(#"Type modifié2", "Updates", each ""),
    #"Type modifié3" = Table.TransformColumnTypes(#"Personnalisée ajoutée1",{{"Updates", type date}})
in
    #"Type modifié3"

验证修正效果 #

以周二为起始日，传入numDays=4：

• 初始状态：Count=1，Date=周二
• 第一次循环：Count=1 <4 → 周三（工作日，Count=2）
• 第二次循环：Count=2 <4 → 周四（工作日，Count=3）
• 第三次循环：Count=3 <4 → 周五（工作日，Count=4）
• 此时Count=4不满足<4的条件，循环停止，最终返回周五，符合预期

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