ACC: "W" Option of the DateDiff() Function Does Not Work (95977)

RESOLUTION

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.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0

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 use the following: 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 Debug Window (or Immediate window in versions 1.x and 2.0), type the following line, and then press ENTER: Note that 11 is returned, the number of work days.