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

ID: Q95977

The information in this article applies to:

Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you use the "w" Weekday option to calculate the number of weekdays between two dates, the DateDiff() function returns the number of weeks, not the number of work days. The "w" option is supposed to function the same as "d" for DateDiff(). It is provided as an option for compatibility with the DatePart() function.

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

   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 Debug Window (or Immediate window in versions 1.x and 2.0), type the following line, and then press ENTER:

   ?DateDiffW(#2/2/97#,#2/18/97#)

Note that 11 is returned, the number of work days.

MORE INFORMATION

Steps to Reproduce Behavior

In the Debug Window, type the following line, and then press ENTER:

   ? DateDiff("W",#2/2/97#,#2/18/97#)

Note that 2 is returned (the number of weeks), not 16 (the number of days) or 11 (the number of work days).

REFERENCES

For more information about the DateDiff function, search the Help Index for "DateDiff," or ask the Microsoft Access 97 Office Assistant.

Additional query words: dates work day diff part

Keywords          : kbprg
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbprb

Last Reviewed: November 20, 1998