ms-access - 查找给定日期范围内的工作日/周末数

Question

我试图找到一些 VBA 代码来使用 Access VBA 确定给定日期范围内的工作日和周末天数。

例如:

Begin Date - 1/1/2012
End Date - 1/31/2012

结果应该是:

Week days - 22
Weekend days - 9

任何人都可以帮忙解决这个问题吗？

Answer 1

这两个函数将计算工作日和周末的天数:

Function NumWeekendDays(dBegin As Date, dEnd As Date) As Long
    Dim iPartial As Integer
    Dim lBeginDay As Long
    Dim lNumWeekendDays As Long

    iPartial = DateDiff("d", dBegin, dEnd + 1) Mod 7
    lBeginDay = 6 - DatePart("w", dBegin, vbMonday)

    lNumWeekendDays = (DateDiff("d", dBegin, dEnd + 1) \ 7) * 2
    If iPartial > 0 And lBeginDay - iPartial < 0 Then
        If lBeginDay = -1 Then
            lNumWeekendDays = lNumWeekendDays + 1
        ElseIf iPartial - lBeginDay = 1 Then
            lNumWeekendDays = lNumWeekendDays + 1
        Else
            lNumWeekendDays = lNumWeekendDays + 2
        End If
    End If

    NumWeekendDays = lNumWeekendDays

End Function

Function NumWeekDays(dBegin As Date, dEnd As Date) As Long
    NumWeekDays = DateDiff("d", dBegin, dEnd + 1) - NumWeekendDays(dBegin, dEnd)
End Function

注意:我发现通过计算 lBeginDay 来计算部分周周末天数是最简单的。变量，以便如果开始日期是星期一， lBeginDay == 5 ...如果开始日期是星期五， lBeginDay == 1等。其他变体也应该有效。