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

ID: Q207795

The information in this article applies to:

Microsoft Access 2000

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

This article applies to a Microsoft Access database (.mdb) and a Microsoft Access project (.adp).

SYMPTOMS

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 work the same way 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.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

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.

MORE INFORMATION

Steps to Reproduce Behavior

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


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

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

REFERENCES

For more information about the DateDiff function, click Microsoft Access Help on the Help menu, type "DateDiff Function" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Additional query words: dates work day diff part prb

Keywords : kbprg kbdta
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb