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

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.

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.

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).