ACC2000: DateAdd() "w" Interval Does Not Work as Expected

View products that this article applies to.

This article was previously published under Q198505

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

Symptoms

When you use the "w" interval (which includes all the days of the week, Sunday through Saturday) to add days to a date, the DateAdd() function adds the total number of days that you specified to the date, instead of adding just the number of workdays (Monday through Friday) to the date, as you might expect.

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.

↑ Back to the top

Cause

The intervals in the DateAdd() function are the same as those used in the DatePart() function. The DatePart() function uses the "w" interval to return the weekday, or day of the week, from a date. The value returned from the DatePart() function is an integer from 1 to 7, representing the days of the week (Sunday through Saturday). When you add days using the "w" interval, weekdays (which include all the days of the week) are added.

To add workdays (Monday through Friday) to a date, use the sample function described in the "Resolution" section.

↑ Back to the top

Resolution

You can use the following sample user-defined function to add workdays, rather than weekdays, to a date:

'**********************************************************
'Declarations section of the module
'**********************************************************

Option Explicit

'==========================================================
' The DateAddW() function provides a workday substitute
' for DateAdd("w", number, date). This function performs
' error checking and ignores fractional Interval values.
'==========================================================
Function DateAddW (ByVal TheDate, ByVal Interval)

   Dim Weeks As Long, OddDays As Long, Temp As String

   If VarType(TheDate) <> 7 Or VarType(Interval) < 2 Or _
              VarType(Interval)  > 5 Then
      DateAddW = TheDate
   ElseIf Interval = 0 Then
      DateAddW = TheDate
   ElseIf Interval > 0 Then
      Interval = Int(Interval)

   ' Make sure TheDate is a workday (round down).

      Temp = Format(TheDate, "ddd")
      If Temp = "Sun" Then
         TheDate = TheDate - 2
      ElseIf Temp = "Sat" Then
         TheDate = TheDate - 1
      End If

   ' Calculate Weeks and OddDays.

      Weeks = Int(Interval / 5)
      OddDays = Interval - (Weeks * 5)
      TheDate = TheDate + (Weeks * 7)

  ' Take OddDays weekend into account.

      If (DatePart("w", TheDate) + OddDays) > 6 Then
         TheDate = TheDate + OddDays + 2
      Else
         TheDate = TheDate + OddDays
      End If

      DateAddW = TheDate
   Else                         ' Interval is < 0
      Interval = Int(-Interval) ' Make positive & subtract later.

   ' Make sure TheDate is a workday (round up).

      Temp = Format(TheDate, "ddd")
      If Temp = "Sun" Then
         TheDate = TheDate + 1
      ElseIf Temp = "Sat" Then
         TheDate = TheDate + 2
      End If

   ' Calculate Weeks and OddDays.

      Weeks = Int(Interval / 5)
      OddDays = Interval - (Weeks * 5)
      TheDate = TheDate - (Weeks * 7)

   ' Take OddDays weekend into account.

      If (DatePart("w", TheDate) - OddDays) > 2 Then
         TheDate = TheDate - OddDays - 2
      Else
         TheDate = TheDate - OddDays
      End If

      DateAddW = TheDate
    End If

End Function

How to Use the DateAddW() Function

Use the DateAddW() function wherever you would use the DateAdd() function. For example, instead of

   DateAdd("w",[StartDate],10)

use:

   DateAddW([StartDate],10)

To test the DateAddW() function, enter the following line in the Debug window, and then press ENTER:

? DateAddW(#2/2/99#,10)

Note that the result is 2/16/99 (10 workdays).

↑ Back to the top

More information

Steps to Reproduce Behavior

Enter the following line in the Debug window, and then press ENTER:

   ? DateAdd("w",#2/2/99#,10)

Note that the result is 2/12/99 (10 days), not 2/16/99 (10 workdays). Note also that the 2/12/99 (10 days) result is correct because the "w" interval includes all the days of the week, Sunday through Saturday.

↑ Back to the top

References

For more information about the DateAdd() function, click Microsoft Access Help on the Help menu, type DateAdd function example in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

↑ Back to the top