Microsoft Access, VBA, and VB6 include a wide range of built-in Date functions, including DateAdd, which calculates the difference between two dates. A common need, however, is to add a number of weekdays to a date, without counting weekend dates.
The following function adds the specified number of weekdays to a date. You can pass a negative number of days subtract.
Public Function AddWeekdays(datDateIn As Date, intDays As Integer) As Date ' Comments: Add or subtract a number of weekdays to a date. ' Weekend dates are not counted in adding/subtracting days. ' Params : datDateIn Starting date ' intDays Number of days to add (negative to subtract) ' Returns : Original date plus the number of weekdays added ' Source : Total Visual SourceBook On Error GoTo PROC_ERR Dim intCounter As Integer Dim intDirection As Integer Dim datNewDate As Date Dim lngWeeks As Long Dim intDaysLeft As Integer datNewDate = datDateIn If intDays > 0 Then intDirection = 1 Else intDirection = -1 End If lngWeeks = Fix(Abs(intDays) / 5) If lngWeeks > 0 Then datNewDate = datNewDate + lngWeeks * 7 * intDirection End If intDaysLeft = Abs(intDays) - lngWeeks * 5 For intCounter = 1 To intDaysLeft datNewDate = datNewDate + 1 * intDirection If intDirection > 0 Then ' Increment date If Weekday(datNewDate) = 7 Then datNewDate = datNewDate + 2 End If Else ' Decrement date If Weekday(datNewDate) = 1 Then datNewDate = datNewDate - 2 End If End If Next intCounter AddWeekdays = datNewDate PROC_EXIT: Exit Function PROC_ERR: MsgBox "Error: " & Err.Number & ". " & Err.Description, , "modDateTime.AddWeekdays" Resume PROC_EXIT End Function
Below is an example of calling the function:
' Calculate the due date of an item as 15 weekdays from today Dim datDueDate As Date datDueDate = AddWeekdays(Date, 15) MsgBox "The item is due on: " & vbCrLf & datDueDate
This code and much more is available in Total Visual SourceBook.
Total Visual SourceBook also include two modules to perform real business day math where a list of holidays is kept either in a table or file. By using the list of holidays you specify, you can take into account weekdays that shouldn't be counted because your organization is closed. The Business Day routines let you:
Supports Access/Office 2016, 2013, 2010 and 2007, and Visual Basic 6.0!
"The code is exactly how I would like to write code and the algorithms used are very efficient and well-documented."
Van T. Dinh, Microsoft MVP