"W" option of the DateDiff() function does not work in Access 2002 (288194)

RESOLUTION

If you are using the DateDiff() function to return the number of days, use "d" instead of "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])
				

NOTE: 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.

? DateDiff("W",#2/1/99#,#2/15/99#)