通过可配置的周末和节假日添加工作日

介绍

我已经找到了一个如何计算两个日期之间的工作日数的示例，但是我还没有找到一种发布方法来添加工作日。 Excel最近添加了此功能，但Microsoft尚未将其提供给Access世界。 因此，我接受了这一挑战。 我创建的功能允许用户指定哪些天不包括在计数中（对于周末），并且还允许将假日列表排除在外。 必须在表格中输入假期才能使用。 我创建了自己的方法来自动从Internet获取此信息，但这是另一篇文章的主题。

桌子

我能想到的最好的方法是列出可用日期，然后递减指定天数的记录。 这意味着我们需要一个日期列表。 不必将它们全部键入一个表中，我们将让Access处理这个问题。 我们需要的是一个包含单个字段的表：

 tbl_ExpansionDigits 
Digit

现在添加10个记录，值从0到9

我们还需要一个假期表。 我的表有一个日期字段和一个名称字段，以便我可以列出假期名称。

 dbo_Holidays 
hDate
hName

表格查询就是这样

实际上，我们只需要一个查询。 这就是给我们约会的魔力。 我的名字叫qryPossibleDates。

SELECT Cdate([tbl_expansiondigits_1]![Digit]
   +[tbl_expansiondigits_2]![Digit]*10
   +[tbl_expansiondigits_3]![Digit]*100
   +[tbl_expansiondigits_4]![Digit]*1000 
   +[tbl_expansiondigits_5]![Digit]*10000) AS CalcDate, Weekday(CalcDate) AS WeekDayNumber
FROM tbl_expansiondigits AS tbl_expansiondigits_1, tbl_expansiondigits AS tbl_expansiondigits_2, tbl_expansiondigits AS tbl_expansiondigits_3, tbl_expansiondigits AS tbl_expansiondigits_4, tbl_expansiondigits AS tbl_expansiondigits_5
WHERE ((([tbl_expansiondigits_1]![Digit]
   +[tbl_expansiondigits_2]![Digit]*10
   +[tbl_expansiondigits_3]![Digit]*100
   +[tbl_expansiondigits_4]![Digit]*1000
   +[tbl_expansiondigits_5]![Digit]*10000)>=36000));

这使我们的日期从1998年7月24日到10/13/2173（共64,000天）加上工作日编号（星期日为1，星期六为7）。 码

首先，我们需要一个函数来将类似二进制的数字转换为十进制值。 我用它来表示二进制标志，以了解从计数中排除哪些天。

Private Function ConvertToDecimal(lngBinary As Long) As Long
Dim strNumber As String
Dim i As Integer
Dim lngAccumulator As Long
Dim n As Integer
Dim s As String
Dim p As Integer 
strNumber = Format(lngBinary, "00000000")
p = 1
For i = 8 To 1 Step -1
    s = Mid(strNumber, p, 1)
    If s = "1" Then
        n = CInt(i) - 1
        lngAccumulator = lngAccumulator + 2 ^ n
    End If
    p = p + 1
Next 
ConvertToDecimal = lngAccumulator 
End Function

现在为主要功能。

Public Function AddDays(dteStart As Date, intInterval As Integer, lngExcludePattern As Long) As Date
Dim lngExcludeDates As Long
Dim strSelect As String
Dim strWhere As String
Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim i As Integer 
If lngExcludePattern <> 0 Then
    lngExcludeDates = ConvertToDecimal(lngExcludePattern)
    strSelect = "SELECT CalcDate As AddedDate FROM qryPossibleDates"
    strWhere = "CalcDate = #" & dteStart & "# Or (CalcDate >= #" & dteStart & "# And CalcDate Not In(SELECT hDate FROM dbo_Holidays WHERE hDate >= #" & dteStart & "#)" 
    If (lngExcludeDates And 64) > 0 Then
        strWhere = strWhere & " And WeekDayNumber <> 1"
    End If 
    If (lngExcludeDates And 32) > 0 Then
        strWhere = strWhere & " And WeekDayNumber <> 2"
    End If 
    If (lngExcludeDates And 16) > 0 Then
        strWhere = strWhere & " And WeekDayNumber <> 3"
    End If 
    If (lngExcludeDates And 8) > 0 Then
        strWhere = strWhere & " And WeekDayNumber <> 4"
    End If 
    If (lngExcludeDates And 4) > 0 Then
        strWhere = strWhere & " And WeekDayNumber <> 5"
    End If 
    If (lngExcludeDates And 2) > 0 Then
        strWhere = strWhere & " And WeekDayNumber <> 6"
    End If 
    If (lngExcludeDates And 1) > 0 Then
        strWhere = strWhere & " And WeekDayNumber <> 7"
    End If 
    strWhere = strWhere & ")"
    strSQL = strSelect & " WHERE " & strWhere
    Debug.Print strSQL 
    Set db = CurrentDb
    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges) 
    rst.Move intInterval 
    AddDays = rst!AddedDate 
    Set db = Nothing
    rst.Close
    Set rst = Nothing 
Else
    AddDays = dteStart + intInterval
End If 
End Function

首先，我测试lngExcludePattern中的值。 如果它是0，那么我只是在计算日历天，它跳到底部。 否则，我将构建一个查询字符串，该字符串从添加条件的上述查询中提取。 您会注意到它以CalcDate =＃“＆dteStart＆”＃“开头 。这是针对dteStart不是查询中包含的日期的情况，因为它确实与所涉及的日期混淆了（我在排除故障后找到了原因我的计数已关闭）。然后，它具有仅显示大于或等于dteStart且不在假期列表中的记录的条件，再次将其过滤为大于或等于dteStart（出于性能）。然后测试使用二进制标记来添加其他条件以排除我们不希望包含的星期几，然后打开记录集并使用.Move方法转到记录集中的正确记录以获取日期。是！ 使用

要使用此功能，请传递要添加的起始日期x天数（dteStart），要添加的天数（intInterval），然后是要排除的天数（lngExcludePattern）。 前两个非常简单，但是“排除模式”需要更多说明。 我将其设置为二进制标志。 第一个标志是周日，最后一个标志是周六。 因此，如果我希望周末为星期六和星期日，那么我的排除模式将为1000001。如果我也希望排除星期三，那么我的模式将为1001001。如果我只希望排除星期六，则为0000001，也可以输入为1，因为它作为长整数传递。 因此，如果我想在1/17/16上增加5天（不包括周六和周日），则可以这样调用我的函数：

=AddDays(#1/17/16#, 5, 1000001)

结论

可能还有其他方法可以执行此操作，但这似乎是最通用的方法，并且实际上可以完成我想要的操作。 如果有人有任何批评，我欢迎他们。

From: https://bytes.com/topic/access/insights/965404-adding-business-days-configurable-weekend-days-holidays