MS ACCESS VBA，包括工作日功能.周末度假时的假期 [英] MS ACCESS VBA, working days function incl. holiday when faling on weekend

问题描述

在部署用于计算工作日的MSDN功能时，除了日期格式问题之外，我还发现了假期计数问题.

When deploying MSDN function for calculating working days, beside a problem with date formatting I found an issue with Holiday count.

计算是正确的，但前提是假日在工作日.如果是星期六或星期天，它也会减去它并产生错误的结果. 错误阅读的说明

Calculation is correct, but only if Holiday is on working day. If it is on a saturday or sunday, it also substract it and produce a false result. illustration of a false reading

工作日功能:

Public Function Workdays(ByRef startDate As Date, ByRef endDate As Date, Optional ByRef strHolidays As String = "Holidays") As Integer
On Error GoTo Workdays_Error
Dim nWeekdays, nHolidays As Integer
Dim strWhere As String

startDate = DateValue(startDate)
endDate = DateValue(endDate)
nWeekdays = Weekdays(startDate, endDate)

If nWeekdays = -1 Then
    Workdays = -1
    GoTo Workdays_Exit
End If

strWhere = "[Holiday] >= #" & Format(startDate, "yyyy\/mm\/dd") & "# AND [Holiday] <= #" & Format(endDate, "yyyy\/mm\/dd") & "#"
nHolidays = DCount(Expr:="[Holiday]", Domain:=strHolidays, Criteria:=strWhere)
Workdays = nWeekdays - nHolidays

Workdays_Exit:
    Exit Function
    Resume Workdays_Exit

End Function

这是计算工作日的函数:

And here is a function for calculating weekdays:

Public Function Weekdays(ByRef startDate As Date, ByRef endDate As Date) As Integer
' Returns the number of weekdays in the period from startDate
' to endDate inclusive. Returns -1 if an error occurs.

On Error GoTo Weekdays_Error
Const ncNumberOfWeekendDays As Integer = 2 'The number of weekend days per week.
Dim varDays As Variant                  'The number of days inclusive.
Dim varWeekendDays As Variant     'The number of weekend days.
Dim dtmX As Date                          'Temporary storage for datetime.

' If the end date is earlier, swap the dates.
If endDate < startDate Then
    dtmX = startDate
    startDate = endDate
    endDate = dtmX
End If

' Calculate the number of days inclusive (+ 1 is to add back startDate).
varDays = DateDiff(Interval:="d", date1:=startDate, date2:=endDate) + 1

' Calculate the number of weekend days.
varWeekendDays = (DateDiff(Interval:="ww", date1:=startDate, date2:=endDate) _
    * ncNumberOfWeekendDays) + IIf(DatePart(Interval:="w", _
    Date:=startDate) = vbSunday, 1, 0) + IIf(DatePart(Interval:="w", Date:=endDate) = vbSaturday, 1, 0)

' Calculate the number of weekdays.
Weekdays = (varDays - varWeekendDays)

Weekdays_Exit:
    Exit Function

Weekdays_Error:
    Weekdays = -1
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Weekdays"
    Resume Weekdays_Exit

请告知如果假日= 1或假日= 7，如何忽略假日. 另外，在较长的一段时间内，可能会有多个假期，周末是否休假.

Please advise how to ignore holiday if holiday = 1 or holiday = 7. Plus, in extended period of time, there could be more than one holidays, falling or not on weekend.

推荐答案

在不深入研究您的代码的情况下，建议您在假期​​表中进行一些假期计算，这些假期属于周末，并且也属于您的工作日范围正在考虑.从(我认为)正确计算出的总数中减去该总数，并且应该考虑到周末假期进行适当的调整.

without delving into your code, I'd suggest doing a count of holidays in your holiday table that fall on weekends and which also fall inside the range of days you are considering. Subtract that total from an (I presume) otherwise correctly calculated total and you should have the proper adjustment taking weekend holidays into account.

这篇关于MS ACCESS VBA，包括工作日功能.周末度假时的假期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！