我将如何使用Excel应用程序.工作日功能跳过周末和假期?

Question

我想知道我将如何使用应用程序。Excel VBA中的工作日函数，以指定代码中的日期计算不能落在周末或假期

这是我的代码:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Columns(46)) Is Nothing Then
If Target.Cells(1).Value = "Performed Audit" Then
Me.Cells(Target.Row, "J").Value = "Performed"
Me.Cells(Target.Row, "K").Value = "Performed"
Me.Cells(Target.Row, "AS").Value = "Post-Audit"
Me.Cells(Target.Row, "AU").Value = Format(Me.Cells(Target.Row, "N"), "mm/dd/yyyy HH:mm:ss")
Me.Cells(Target.Row, "AV").Value = "Issue Audit Report"
Me.Cells(Target.Row, "AW").Value = Format(Me.Cells(Target.Row, "N") + 20, "mm/dd/yyyy 
HH:mm:ss")
Me.Cells(Target.Row, "AZ").Value = Format(Me.Cells(Target.Row, "N") + 20, "mm/dd/yyyy 
HH:mm:ss")
Me.Cells(Target.Row, "BA").Value = Format(Me.Cells(Target.Row, "N") + 20, "mm/dd/yyyy 
HH:mm:ss")
End If
End Sub

特别地，通过添加20天来计算的日期不能落在周末或下面显示的假日之一

劳动节星期一，2022-09-05，

感恩节星期四，2022-11-24，

感恩节后的第二天，星期五，2022-11-25，

圣诞节(已观察)星期五，2022-12-23，

假期星期一，2022-12-26，

假期星期二，2022-12-27，

假期星期三，2022-12-28，

假期，2022-12-29，

假期星期五，2022-12-30

Answer 1

请尝试下一次更新的事件。它使用求值的Excel函数WORKDAY.INTL。由于WORKDAY.INTL不返回时间(它返回的是相当于一天的长时间，而不是包含时间的Double)，我使用了一个技巧，从计算单元中提取时间，并在计算后添加:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim arrHolidays, tmp As Single, rngN As Range, d As Date

If Not Intersect(Target, Me.Columns(46)) Is Nothing Then
If Target.cells(1).value = "Performed Audit" Then
'place in an array all national holidays:
arrHolidays = Array(CLng(CDate("2022-09-05")), CLng(CDate("2022-11-24")), CLng(CDate("2022-11-25")), CLng(CDate("2022-12-23")), _
CLng(CDate("2022-12-26")), CLng(CDate("2022-12-28")), CLng(CDate("2022-12-29")), CLng(CDate("2022-12-30")))
On Error GoTo SafeExit
Set rngN = Me.cells(Target.row, "N"):  d = rngN.value
tmp = CDbl(d) - Int(d) 'place the time in a variable. Workday does not return hours, minutes, seconds...

Application.EnableEvents = False: Application.Calculation = xlCalculationManual
Me.cells(Target.row, "J").value = "Performed"
Me.cells(Target.row, "K").value = "Performed"
Me.cells(Target.row, "AS").value = "Post-Audit"
Me.cells(Target.row, "AU").value = Format(Me.cells(Target.row, "N"), "mm/dd/yyyy HH:mm:ss")
Me.cells(Target.row, "AV").value = "Issue Audit Report"
Me.cells(Target.row, "AW").value = Format(Evaluate("WORKDAY.INTL(" & Me.cells(Target.row, "N").Address & _
",20,1,{" & Join(arrHolidays, ",") & "})") + tmp, "mm/dd/yyyy  HH:mm:ss")
Me.cells(Target.row, "AZ").value = Format(Evaluate("WORKDAY.INTL(" & Me.cells(Target.row, "N").Address & _
",20,1,{" & Join(arrHolidays, ",") & "})") + tmp, "mm/dd/yyyy  HH:mm:ss")
Me.cells(Target.row, "BA").value = Format(Evaluate("WORKDAY.INTL(" & Me.cells(Target.row, "N").Address & _
",20,1,{" & Join(arrHolidays, ",") & "})") + tmp, "mm/dd/yyyy  HH:mm:ss")
Application.EnableEvents = True: Application.Calculation = xlCalculationAutomatic
End If
End If
Exit Sub
SafeExit: 'reenable events in case of an error:
Application.EnableEvents = True
MsgBox err.Description, vbCritical, err.Number
End Sub

或者没有Evaluate，使用WorkDay_Intl和Holidays数组。更紧凑和唯一的标准VBA:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Columns(46)) Is Nothing Then
Dim arrHolidays, tmp As Single, rngN As Range, d As Date

If Target.cells(1).value = "Performed Audit" Then
'place in an array all national holidays:
arrHolidays = Array(CLng(CDate("2022-09-05")), CLng(CDate("2022-11-24")), CLng(CDate("2022-11-25")), CLng(CDate("2022-12-23")), _
CLng(CDate("2022-12-26")), CLng(CDate("2022-12-28")), CLng(CDate("2022-12-29")), CLng(CDate("2022-12-30")))
On Error GoTo SafeExit
Set rngN = Me.cells(Target.row, "N"):  d = rngN.value
tmp = CDbl(d) - Int(d) 'place the time in a variable. Workday does not return hours, minutes, seconds...

Application.EnableEvents = False: Application.Calculation = xlCalculationManual
Me.cells(Target.row, "J").value = "Performed"
Me.cells(Target.row, "K").value = "Performed"
Me.cells(Target.row, "AS").value = "Post-Audit"
Me.cells(Target.row, "AU").value = Format(Me.cells(Target.row, "N"), "mm/dd/yyyy HH:mm:ss")
Me.cells(Target.row, "AV").value = "Issue Audit Report"
Me.cells(Target.row, "AW").value = Format(WorksheetFunction.WorkDay_Intl(d, 20, 1, arrHolidays) + tmp, "mm/dd/yyyy  HH:mm:ss")
Me.cells(Target.row, "AZ").value = Format(WorksheetFunction.WorkDay_Intl(d, 20, 1, arrHolidays) + tmp, "mm/dd/yyyy  HH:mm:ss")
Me.cells(Target.row, "BA").value = Format(WorksheetFunction.WorkDay_Intl(d, 20, 1, arrHolidays) + tmp, "mm/dd/yyyy  HH:mm:ss")
Application.EnableEvents = True: Application.Calculation = xlCalculationAutomatic
End If
End If

Exit Sub

SafeExit: 'reenable events in case of an error:
Application.EnableEvents = True: Application.Calculation = xlCalculationAutomatic
MsgBox err.Description, vbCritical, err.Number
End Sub

请在测试后发送一些反馈。