If you are using the
DateDiff() function to return the number of days, substitute "d" for "w". You can use the Visual Basic code in this article to return the number of work days rather than the number of days.
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.
The following code provides a function, DateDiffW(), that calculates
the number of work days between two dates:
Function DateDiffW(BegDate, EndDate)
Const SUNDAY = 1
Const SATURDAY = 7
Dim NumWeeks As Integer
If BegDate > EndDate Then
DateDiffW= 0
Else
Select Case Weekday(BegDate)
Case SUNDAY : BegDate = BegDate + 1
Case SATURDAY : BegDate = BegDate + 2
End Select
Select Case Weekday(EndDate)
Case SUNDAY : EndDate = EndDate - 2
Case SATURDAY : EndDate = EndDate - 1
End Select
NumWeeks = DateDiff("ww", BegDate, EndDate)
DateDiffW= NumWeeks * 5 + Weekday(EndDate) - Weekday(BegDate)
End If
End Function
How to Use the DateDiffW() Function
Use the DateDiffW() function wherever you would use
DateDiff(). Instead of
DateDiff("W",[StartDate],[EndDate])
use the following:
DateDiffW([StartDate],[EndDate])
: This function returns the days UP TO the ending date, not UP TO and INCLUDING the ending date.
Steps to Test the DateDiffW() Function
In the Immediate Window, type the following line, and then press ENTER:
?DateDiffW(#2/1/99#,#2/15/99#)
Note that 10 is returned, the number of work days.