为什么datediff间隔为“工作日"?返回间隔基于周而不是天减去周末? [英] Why is datediff interval "weekday" returning interval based on weeks and not days minus weekends?
问题描述
我正在使用表达式生成器来给我一个计算值.看起来像这样:
I'm using the expression builder to give me a calculated value. It looks like this:
=DateDiff("w",Date(),[Latest_Call_Date])
但是,这给了我结果,就好像它是根据周而不是天减去周末进行计算一样.当我尝试相同的功能,但间隔设置为天(d)而不是工作日(w)时,我得到了预期的结果(当然,包括星期六和星期日,我不希望将其包括在计算中).因此,例如,本周每天都有1,2,3,4,5天的差异,但它们在工作日都返回零.该公式是:
However, this is giving me results as if it were doing a calculation based on weeks instead of days minus weekends . When I try the same function, but with the interval set to days (d) instead of weekdays (w) I get the expected results (but of course, including Saturdays and Sundays, which I do not wish to include in the calculation). So, for example, for everyday this week I'm getting 1,2,3,4,5 day differences but they all return zero with weekdays. That formula is:
=DateDiff("d",Date(),[Latest_Call_Date])
为了使工作日"间隔正常工作,我还需要做其他事情吗?
Is there something else I have to do to get "weekdays" interval to work?
我正在使用.accdb文件格式的Access 2013.
I'm using Access 2013, in an .accdb file format.
推荐答案
平日(不包括周末(节假日))通常标记为工作日.
Weekdays excluding weekends (and holidays) are usually labelled workdays.
DateDiff("w",..)返回两个日期之间的工作日计数,而
DateDiff("w", ..) returns the count of a weekday between two dates, while
DateDiff("ww",..)返回两个日期之间的日历周差异.
DateDiff("ww", ..) returns the difference in calendar weeks between two dates.
计算工作日需要更多时间.该函数将执行以下操作:
Counting workdays takes a little more. This function will do:
Public Function DateDiffWorkdays( _
ByVal datDate1 As Date, _
ByVal datDate2 As Date, _
Optional ByVal booWorkOnHolidays As Boolean) _
As Long
' Calculates the count of workdays between datDate1 and datDate2.
' 2014-10-03. Cactus Data ApS, CPH
Dim aHolidays() As Date
Dim lngDiff As Long
Dim lngSign As Long
Dim lngHoliday As Long
lngSign = Sgn(DateDiff("d", datDate1, datDate2))
If lngSign <> 0 Then
If booWorkOnHolidays = True Then
' Holidays are workdays.
Else
' Retrieve array with holidays between datDate1 and datDate2.
aHolidays = GetHolidays(datDate1, datDate2)
End If
Do Until DateDiff("d", datDate1, datDate2) = 0
Select Case Weekday(datDate1)
Case vbSaturday, vbSunday
' Skip weekend.
Case Else
' Check for holidays to skip.
' Ignore error when using LBound and UBound on an unassigned array.
On Error Resume Next
For lngHoliday = LBound(aHolidays) To UBound(aHolidays)
If Err.Number > 0 Then
' No holidays between datDate1 and datDate2.
ElseIf DateDiff("d", datDate1, aHolidays(lngHoliday)) = 0 Then
' This datDate1 hits a holiday.
' Subtract one day before adding one after the loop.
lngDiff = lngDiff - lngSign
Exit For
End If
Next
On Error GoTo 0
lngDiff = lngDiff + lngSign
End Select
datDate1 = DateAdd("d", lngSign, datDate1)
Loop
End If
DateDiffWorkdays = lngDiff
End Function
还有假期,如果有一天您需要它:
And the Holidays, should you need it some day:
Public Function GetHolidays( _
ByVal datDate1 As Date, _
ByVal datDate2 As Date, _
Optional ByVal booDesc As Boolean) _
As Date()
' Finds the count of holidays between datDate1 and datDate2.
' The holidays are returned as an array of dates.
' DAO objects are declared static to speed up repeated calls with identical date parameters.
' 2014-10-03. Cactus Data ApS, CPH
' The table that holds the holidays.
Const cstrTable As String = "tblHoliday"
' The field of the table that holds the dates of the holidays.
Const cstrField As String = "HolidayDate"
' Constants for the arrays.
Const clngDimRecordCount As Long = 2
Const clngDimFieldOne As Long = 0
Static dbs As DAO.Database
Static rst As DAO.Recordset
Static datDate1Last As Date
Static datDate2Last As Date
Dim adatDays() As Date
Dim avarDays As Variant
Dim strSQL As String
Dim strDate1 As String
Dim strDate2 As String
Dim strOrder As String
Dim lngDays As Long
If DateDiff("d", datDate1, datDate1Last) <> 0 Or DateDiff("d", datDate2, datDate2Last) <> 0 Then
' datDate1 or datDate2 has changed since the last call.
strDate1 = Format(datDate1, "\#yyyy\/mm\/dd\#")
strDate2 = Format(datDate2, "\#yyyy\/mm\/dd\#")
strOrder = Format(booDesc, "\A\s\c;\D\e\s\c")
strSQL = "Select " & cstrField & " From " & cstrTable & " " & _
"Where " & cstrField & " Between " & strDate1 & " And " & strDate2 & " " & _
"Order By 1 " & strOrder
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
' Save the current set of date parameters.
datDate1Last = datDate1
datDate2Last = datDate2
End If
lngDays = rst.RecordCount
If lngDays = 0 Then
' Leave adatDays() as an unassigned array.
Else
ReDim adatDays(lngDays - 1)
' As repeated calls may happen, do a movefirst.
rst.MoveFirst
avarDays = rst.GetRows(lngDays)
' rst is now positioned at the last record.
For lngDays = LBound(avarDays, clngDimRecordCount) To UBound(avarDays, clngDimRecordCount)
adatDays(lngDays) = avarDays(clngDimFieldOne, lngDays)
Next
End If
' DAO objects are static.
' Set rst = Nothing
' Set dbs = Nothing
GetHolidays = adatDays()
End Function
这篇关于为什么datediff间隔为“工作日"?返回间隔基于周而不是天减去周末?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!