Excel产品周期日期自动调整：含节假日时结束日期+1方案问询

首先需要准备节假日列表：在Excel中新建一个工作表（比如命名为节假日），在A列录入未来10年的所有节假日（日期格式，如2024/1/1、2024/12/24），假设数据范围是节假日!$A$2:$A$120（10年×12个节假日）。

方案一：普通Excel公式实现 #

无需VBA，直接用内置函数统计节假日并调整日期：

1. 开始日期（确保为非节假日） #

如果原开始日期（J2）是节假日，自动顺延至下一个工作日：

=TEXT(WORKDAY(J2-1,1,节假日!$A$2:$A$120),"mmm dd")

WORKDAY函数会从J2的前一天开始，返回第1个避开节假日的工作日。

2. 测试日期范围（自动延长包含节假日的周期） #

原测试周期为开始日+1到开始日+15，若区间内有n个节假日，结束日期自动加n天：

=TEXT(C2+1,"mmm dd")&" - "&TEXT(C2+15+COUNTIFS(节假日!$A$2:$A$120,">="&C2+1,节假日!$A$2:$A$120,"<="&C2+15),"mmm dd")

COUNTIFS统计测试区间内的节假日数量，直接加到原结束日期上。

3. 回归日期范围（同测试日期逻辑） #

原回归周期为开始日+16到开始日+21，自动延长节假日对应的天数：

=TEXT(C2+16,"mmm dd")&" - "&TEXT(C2+21+COUNTIFS(节假日!$A$2:$A$120,">="&C2+16,节假日!$A$2:$A$120,"<="&C2+21),"mmm dd")

4. 完成日期 #

直接引用调整后的测试结束日期（需将文本格式转回日期值，再设置单元格格式为mmm dd）：

=DATEVALUE(D2)

（假设测试日期在D2单元格）

5. 下一行开始日期 #

接在当前完成日期之后，确保为非节假日：

=TEXT(WORKDAY(E2,1,节假日!$A$2:$A$120),"mmm dd")

（假设完成日期在E2单元格）

方案二：VBA自定义函数（更简洁的公式调用） #

通过自定义函数封装逻辑，让公式更易读：

1. 按Alt+F11打开VBA编辑器，插入模块，粘贴以下代码：

' 计算调整后的结束日期：原日期+指定天数+区间内节假日数，且确保最终日期非节假日
Function AdjustEndDate(startDate As Date, addDays As Integer, holidays As Range) As Date
    Dim originalEnd As Date
    Dim holidayCount As Integer
    originalEnd = startDate + addDays
    ' 统计区间内节假日数量
    holidayCount = Application.WorksheetFunction.CountIfs(holidays, ">=" & startDate + 1, holidays, "<=" & originalEnd)
    ' 调整结束日期
    AdjustEndDate = originalEnd + holidayCount
    ' 若调整后的日期仍为节假日，继续顺延
    Do While Application.WorksheetFunction.CountIf(holidays, AdjustEndDate) > 0
        AdjustEndDate = AdjustEndDate + 1
    Loop
End Function

' 返回指定日期之后的第一个非节假日工作日
Function NextValidWorkday(inputDate As Date, holidays As Range) As Date
    NextValidWorkday = Application.WorksheetFunction.WorkDay(inputDate - 1, 1, holidays)
End Function

2. 返回Excel工作表，使用自定义函数：

• 开始日期：=TEXT(NextValidWorkday(J2,节假日!$A$2:$A$120),"mmm dd")
• 测试日期：=TEXT(C2+1,"mmm dd")&" - "&TEXT(AdjustEndDate(C2,15,节假日!$A$2:$A$120),"mmm dd")
• 回归日期：=TEXT(C2+16,"mmm dd")&" - "&TEXT(AdjustEndDate(C2,21,节假日!$A$2:$A$120),"mmm dd")
• 完成日期：=AdjustEndDate(C2,15,节假日!$A$2:$A$120)
• 下一行开始日期：=TEXT(NextValidWorkday(E2,节假日!$A$2:$A$120),"mmm dd")

方案三：Excel动态数组+命名范围（优化维护性） #

将节假日列表定义为命名范围，配合动态数组自动扩展（适用于Excel 365/2021）：

1. 定义命名范围：

   • 点击公式选项卡→定义名称，名称设为Holidays，引用位置输入=OFFSET(节假日!$A$2,0,0,COUNTA(节假日!$A:$A)-1,1)，这样节假日列表新增日期时，范围会自动扩展。

2. 后续公式直接使用Holidays替代具体范围，比如测试日期公式变为：

=TEXT(C2+1,"mmm dd")&" - "&TEXT(C2+15+COUNTIFS(Holidays,">="&C2+1,Holidays,"<="&C2+15),"mmm dd")

其他公式同理替换，后续维护只需在节假日工作表添加新日期即可。

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