将工作日添加到开始日期以创建结束日期(包括银行假日)

Question

我已经修改了这个访问函数，这样我就可以计算出给定工作日的结束日期。

然而，我需要能够把英国银行假期考虑在内。

有谁知道我该怎么做吗?

Public Function CountDays(startDate As Date, NoOfDays As Integer) As Date
' Function to count no of working days
Dim tmpNo As Integer
Dim tmpDate As Date
Dim tmpStartDate As Date
Dim i As Integer
tmpNo = NoOfDays
tmpStartDate = startDate
tmpDate = startDate
i = 0
Do Until i = NoOfDays
If Weekday(tmpDate) = 1 Or Weekday(tmpDate) = 7 Then
tmpNo = tmpNo + 1
Else
i = i + 1
End If
tmpDate = tmpDate + 1
Loop
CountDays = DateAdd("d", tmpNo, tmpStartDate)
End Function

欢呼罗斯

Answer 1

无需每天循环。稍微计算一下就可以得到工作日的数量，然后通过表查找该时间段内的假日数量。所以:

TotalDays = DateDiff("d", startDate, endDate) + 1
TotalWeekends = (DateDiff("ww", startDate, endDate) * 2) + _ 
((DatePart("w", startDate) = vbSunday) *-1) + _ 
((DatePart("w", endDate) = vbSaturday) *-1)
TotalHolidays = DCount("*","tblHolidays","Holiday Between #" & startdate & "# And #" & enddate & "#")
TotalWorkingDays = TotalDays - TotalWeekends - TotalHolidays 

顺便说一下，如果您在日期周围使用#，Access将假定它是美国格式。

Answer 2

由于英国的银行假日每年都不相同，因此您需要创建一个表来存储这些日期，然后在代码中检查此表，并在存在日期时增加tmpNo值。

通过添加额外的'Else If'子句来更改代码，如下所示:

Do Until i = NoOfDays
    If Weekday(tmpDate) = 1 Or Weekday(tmpDate) = 7 Then
        tmpNo = tmpNo + 1
    Else If DCount("*","tblExceptionDates","dtmDate = #" & tmpDate & "#") > 0 Then
        tmpNo = tmpNo + 1
    Else
        i = i + 1
    End If
    tmpDate = tmpDate + 1
Loop