How do you determine a date after x number of business days?

I need to add 1, 2, 5, 15, or 44 business days after a given date. How would I do that?

I have the basic code for everyday here:

Private Sub OpenDate_AfterUpdate()
    Me.FollowupDate = DateAdd("d", Me.cboPriority.Column(2), Me.OpenDate)
   
End Sub

The combo box in Me.cboPriority.Column(2) contains a list of the number of days to add.
LVL 1
David BigelowStaff Operations SpecialistAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
the formula
  Me.FollowupDate = DateAdd("d", Me.cboPriority.Column(2), Me.OpenDate)

is correct,  Me.cboPriority.Column(2),  refers to the 3rd (third) column of the combo.
but  perhaps the list of days is on the second column of the combo box, so you need to use

  Me.FollowupDate = DateAdd("d", Me.cboPriority.Column(1), Me.OpenDate)


... also you need to set the column count  property of the combo to
column count  2 or what ever the number of columns the Row Source of the combo have.
Jim HornMicrosoft SQL Server Data DudeCommented:
I have an article called SQL Server Calendar Table that is a demo of how to build your own table of days, that handles among other things custom business days.   The T-SQL code won't help you in Access, but it'll give you ideas on how to pull this off.

Neither SQL Server nor Access have a way to handle custom business days, as everyone's definition will vary.

Hope this helps.
Jim
John TsioumprisSoftware & Systems EngineerCommented:
I think you need something like this
Public Function AddBussinessDays(startDate As Date, BussinessDays As Integer)
    Dim bussinessDayCounter As Integer
    Dim currentDate As Date
    currentDate = startDate
    For bussinessDayCounter = 1 To BussinessDays
        currentDate = DateAdd("d", 1, currentDate)
        While Weekday(currentDate) = vbSaturday Or Weekday(currentDate) = vbSunday
            currentDate = DateAdd("d", 1, currentDate)
        Wend
        
    Next
    AddBussinessDays = currentDate
End Function

Open in new window

If you need also to check for holidays then you can modify the While condition (e.g :
While Weekday(currentDate) = vbSaturday Or Weekday(currentDate) = vbSunday or IsHoliday(currentdate) 

Open in new window

Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Gustav BrockCIOCommented:
Here is how to calculate this the fast way:
Public Function DateAddWorkdays( _
    ByVal lngNumber As Long, _
    ByVal datDate As Date, _
    Optional ByVal booWorkOnHolidays As Boolean) _
    As Date
    
'   Adds lngNumber of workdays to datDate.
'   2014-10-03. Cactus Data ApS, CPH
    
    ' Calendar days per week.
    Const clngWeekdayCount  As Long = 7
    ' Workdays per week.
    Const clngWeekWorkdays  As Long = 5
    ' Average count of holidays per week maximum.
    Const clngWeekHolidays  As Long = 1
    ' Maximum valid date value.
    Const cdatDateRangeMax  As Date = #12/31/9999#
    ' Minimum valid date value.
    Const cdatDateRangeMin  As Date = #1/1/100#
    
    Dim aHolidays() As Date

    Dim lngDays     As Long
    Dim lngDiff     As Long
    Dim lngDiffMax  As Long
    Dim lngSign     As Long
    Dim datDate1    As Date
    Dim datDate2    As Date
    Dim datLimit    As Date
    Dim lngHoliday  As Long
    

    lngSign = Sgn(lngNumber)
    datDate2 = datDate
    
    If lngSign <> 0 Then
        If booWorkOnHolidays = True Then
            ' Holidays are workdays.
        Else
            ' Retrieve array with holidays between datDate and datDate + lngDiffMax.
            ' Calculate the maximum calendar days per workweek.
            lngDiffMax = lngNumber * clngWeekdayCount / (clngWeekWorkdays - clngWeekHolidays)
            ' Add one week to cover cases where a week contains multiple holidays.
            lngDiffMax = lngDiffMax + Sgn(lngDiffMax) * clngWeekdayCount
            datDate1 = DateAdd("d", lngDiffMax, datDate)
            aHolidays = GetHolidays(datDate, datDate1)
        End If
        Do Until lngDays = lngNumber
            If lngSign = 1 Then
                datLimit = cdatDateRangeMax
            Else
                datLimit = cdatDateRangeMin
            End If
            If DateDiff("d", DateAdd("d", lngDiff, datDate), datLimit) = 0 Then
                ' Limit of date range has been reached.
                Exit Do
            End If
            
            lngDiff = lngDiff + lngSign
            datDate2 = DateAdd("d", lngDiff, datDate)
            Select Case Weekday(datDate2)
                Case vbSaturday, vbSunday
                    ' Skip weekend.
                Case Else
                    ' Check for holidays to skip.
                    ' Ignore error when using LBound and UBound on an unassigned array.
                    On Error Resume Next
                    For lngHoliday = LBound(aHolidays) To UBound(aHolidays)
                        If Err.Number > 0 Then
                            ' No holidays between datDate and datDate1.
                        ElseIf DateDiff("d", datDate2, aHolidays(lngHoliday)) = 0 Then
                            ' This datDate2 hits a holiday.
                            ' Subtract one day before adding one after the loop.
                            lngDays = lngDays - lngSign
                            Exit For
                        End If
                    Next
                    On Error GoTo 0
                    lngDays = lngDays + lngSign
            End Select
        Loop
    End If
    
    DateAddWorkdays = datDate2

End Function


Public Function GetHolidays( _
    ByVal datDate1 As Date, _
    ByVal datDate2 As Date, _
    Optional ByVal booDesc As Boolean) _
    As Date()
    
'   Finds the count of holidays between datDate1 and datDate2.
'   The holidays are returned as an array of dates.
'   DAO objects are declared static to speed up repeated calls with identical date parameters.
'   2014-10-03. Cactus Data ApS, CPH
    
    ' The table that holds the holidays.
    Const cstrTable             As String = "tblHoliday"
    ' The field of the table that holds the dates of the holidays.
    Const cstrField             As String = "HolidayDate"
    ' Constants for the arrays.
    Const clngDimRecordCount    As Long = 2
    Const clngDimFieldOne       As Long = 0
    
    Static dbs              As DAO.Database
    Static rst              As DAO.Recordset
    
    Static datDate1Last     As Date
    Static datDate2Last     As Date
    
    Dim adatDays()  As Date
    Dim avarDays    As Variant
    
    Dim strSQL      As String
    Dim strDate1    As String
    Dim strDate2    As String
    Dim strOrder    As String
    Dim lngDays     As Long
    
    If DateDiff("d", datDate1, datDate1Last) <> 0 Or DateDiff("d", datDate2, datDate2Last) <> 0 Then
        ' datDate1 or datDate2 has changed since the last call.
        strDate1 = Format(datDate1, "\#yyyy\/mm\/dd\#")
        strDate2 = Format(datDate2, "\#yyyy\/mm\/dd\#")
        strOrder = Format(booDesc, "\A\s\c;\D\e\s\c")
        
        strSQL = "Select " & cstrField & " From " & cstrTable & " " & _
            "Where " & cstrField & " Between " & strDate1 & " And " & strDate2 & " " & _
            "Order By 1 " & strOrder
        
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
        
        ' Save the current set of date parameters.
        datDate1Last = datDate1
        datDate2Last = datDate2
    End If
        
    lngDays = rst.RecordCount
    If lngDays = 0 Then
        ' Leave adatDays() as an unassigned array.
    Else
        ReDim adatDays(lngDays - 1)
        ' As repeated calls may happen, do a movefirst.
        rst.MoveFirst
        avarDays = rst.GetRows(lngDays)
        ' rst is now positioned at the last record.
        For lngDays = LBound(avarDays, clngDimRecordCount) To UBound(avarDays, clngDimRecordCount)
            adatDays(lngDays) = avarDays(clngDimFieldOne, lngDays)
        Next
    End If
        
    ' DAO objects are static.
    ' Set rst = Nothing
    ' Set dbs = Nothing
    
    GetHolidays = adatDays()
    
End Function

Open in new window

Note that you will need a table to hold your holidays:

    ' The table that holds the holidays.
    Const cstrTable             As String = "tblHoliday"
    ' The field of the table that holds the dates of the holidays.
    Const cstrField             As String = "HolidayDate"


Your code could then read:

   Me!FollowupDate.Value = DateAddWorkdays(Val(Me!cboPriority.Column(2)), Me!OpenDate.Value)

/gustav

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IrogSintaCommented:
If you aren't concerned at all with Holidays and your starting date is always on a Workday, here's a formula I worked out:
StartDate + DaysToAdd + Int((DaysToAdd + (Weekday(StartDate, vbMonday) - 1)) / 5) * 2

You can use this in a function like so:
Public Function AddBusinessDays(StartDate As Date, DaysToAdd As Integer) As Date
    AddBusinessDays = StartDate + DaysToAdd + Int((DaysToAdd + (Weekday(StartDate, vbMonday) - 1)) / 5) * 2
    
End Function

Open in new window

If your starting date can fall on a weekend, you can modify the function above a bit:
Public Function AddBusinessDays(StartDate As Date, DaysToAdd As Integer) As Date
    AddBusinessDays = StartDate + DaysToAdd + Int((DaysToAdd + (Weekday(StartDate, vbMonday) - 1)) / 5) * 2

    Select Case Format(StartDate, "ddd")
        Case "Sat"
            AddBusinessDays = AddBusinessDays - 1
        Case "Sun"
            AddBusinessDays = AddBusinessDays - 2
    End Select
    
End Function

Open in new window

Ron
David BigelowStaff Operations SpecialistAuthor Commented:
Before I make a decision about adding a function that accounts for holidays, does anyone know whether there is coding to keep recurring holidays on the current year. For example, July 4, or the 4th Thursday of November. Maybe the holidays table is better kept up to date by linking to an Excel spreadsheet and using the recurring/current options there.
Gustav BrockCIOCommented:
I would use a table.

/gustav
David BigelowStaff Operations SpecialistAuthor Commented:
@Gustav,

When I use the two public functions and then the AfterUpdate code, I get the message,

"Compile Error: Expected variable or procedure, not module."

Never mind. When I left it as part of the AfterUpdate, it worked. Still looking at a few more things.
David BigelowStaff Operations SpecialistAuthor Commented:
Thank you, everyone, for posting. As complicated as it is, or can be, I was surprised at the amount of feedback I received.
Gustav BrockCIOCommented:
You are welcome!

/gustav
IrogSintaCommented:
Here's a modification of my function that accounts for holidays stored in a table called tbl_Holidays which has a field called DT for storing the holidays.  Though Gustav mentioned his function is fast, I believe this is faster because there is no looping involved as in both Gustav's and John's code .  Can you give it a try and let me know if you come up with the same results?

Public Function AddBusinessDays(StartDate As Date, DaysToAdd As Integer) As Date
    Dim NumHolidays As Integer
    Dim EndDate As Date
    
    EndDate = StartDate + DaysToAdd + Int((DaysToAdd + (Weekday(StartDate, vbMonday) - 1)) / 5) * 2

    'account for StartDate falling on a weekend
    Select Case Format(StartDate, "ddd")
        Case "Sat"
            EndDate = EndDate - 1
        Case "Sun"
            EndDate = EndDate - 2
    End Select
    
    'account for holidays within starting and ending dates
    NumHolidays = dcount("*", "tbl_Holidays", "[DT]>=#" & StartDate & "# And [DT]<=#" & EndDate & "#")
        
    AddBusinessDays = EndDate + NumHolidays
    
End Function

Open in new window

Ron
IrogSintaCommented:
@David Bigelow,
 Just wondering if you've had a chance to try the last function I posted.

@Gustav,
Can I get your opinion on this function that doesn't require looping through the days?

Ron
David BigelowStaff Operations SpecialistAuthor Commented:
Sorry about the delay. I'll check it in the morning.
Gustav BrockCIOCommented:
Ron, I had a similar idea back in 1999, but had to modify it several times to overcome "nasty" combinations of parameters where start or end will collide with weekends and holidays. I have posted it multiple times through the years.

Last year I decided to rewrite it to speed up repeated calls to the holiday table. Also, I'd found that mostly you add only a few days - for longer intervals you typically count in weeks or months. Further, computers are much faster today. Finally, a loop - as shown - is so simple that it cannot fail getting the correct result no matter if holidays fall in weekends or are juxtaposed weekends.

My function above runs in 10 ms or less even for a 1500 day count and less than 1 ms for a 14 day count, so there is no large speed gain to hunt.

You should "internationalize" your function. Use Weekday to check for weekend, as using the English weekday names will fail in any non-English environment. Likewise, force an unambiguous format on date value expressions in SQL criteria:

    "[DT] >= #" & Format(StartDate, "yyyy\/mm\/dd") & "# ... "

/gustav
IrogSintaCommented:
@Gustav,
The function I wrote does take care of whether the start or end date collides with a weekend or holiday.  Your idea about internationalizing it is a good one.  I'm not too familiar though with the date standard in other countries.  Will that syntax you gave work everywhere?  I appreciate and value your feedback.  Thanks.

Ron
Gustav BrockCIOCommented:
Yes, Weekday returns a numeric, and the SQL format works everywhere.

/gustav
David BigelowStaff Operations SpecialistAuthor Commented:
@Ron,

When I add this to the AfterUpdate:

Private Sub OpenDate_AfterUpdate()

    Me!FollowupDate = AddBusinessDays(Me!cboPriority.Column(2), Me!OpenDate.Value)
End Sub

Public Function AddBusinessDays(StartDate As Date, DaysToAdd As Integer) As Date
    Dim NumHolidays As Integer
    Dim EndDate As Date
   
    EndDate = StartDate + DaysToAdd + Int((DaysToAdd + (Weekday(StartDate, vbMonday) - 1)) / 5) * 2

    'account for StartDate falling on a weekend
    Select Case Format(StartDate, "ddd")
        Case "Sat"
            EndDate = EndDate - 1
        Case "Sun"
            EndDate = EndDate - 2
    End Select
   
    'account for holidays within starting and ending dates
    NumHolidays = DCount("*", "tblHoliday", "[HolidayDate]>=#" & StartDate & "# And [HolidayDate]<=#" & EndDate & "#")
       
    AddBusinessDays = EndDate + NumHolidays
   
End Function
________________________________

I get this:

Runtime error '6':
Overflow
IrogSintaCommented:
David, the function takes in the Starting Date first, and then the number of days to add... so your syntax should be:
Me!FollowupDate = AddBusinessDays(Me!OpenDate.Value, Me!cboPriority.Column(2))

Or you could reverse the arguments in the function header:
Public Function AddBusinessDays( DaysToAdd As Integer, StartDate As Date) As Date
David BigelowStaff Operations SpecialistAuthor Commented:
@Ron,

Okay, it's working now. Runtimes are about the same: yours might be faster. It's hard to say. Power of suggestion?
David BigelowStaff Operations SpecialistAuthor Commented:
IrogSintaCommented:
It probably depends on the number of days you're adding.  If you test it by adding a few thousand days, you may see a speed difference.  Obviously this is not something you would normally do but if you just do a quick test, I'm sure you'll see a slight difference.  Again, this really doesn't matter much but if the results are the same, I personally tend to prefer smaller code.  

Ron
David BigelowStaff Operations SpecialistAuthor Commented:
Ron,
I put in 2000 days and couldn't see a difference.
GrahamMandenoCommented:
@Ron

In addition to Gustav's comments, there is a semantic problem with this line:

 AddBusinessDays = EndDate + NumHolidays

The problem is that there is no check to ensure that there are no non-business days within NumHolidays of the EndDate that you have calculated.

-- Graham Mandeno [Access MVP since 1996]
IrogSintaCommented:
Graham, you're absolutely right!  Thanks for pointing that out.  I modified my DCount syntax to take those days into consideration.  Here's the corrected function that also includes Gustav's suggestions:

Public Function AddBusinessDays(StartDate As Date, DaysToAdd As Integer) As Date
    Dim NumHolidays As Integer
    Dim EndDate As Date
    
    EndDate = StartDate + DaysToAdd + Int((DaysToAdd + (Weekday(StartDate, vbMonday) - 1)) / 5) * 2

    'account for StartDate falling on a weekend
    Select Case Weekday(StartDate, vbMonday)
        Case 6
            EndDate = EndDate - 1
        Case 7
            EndDate = EndDate - 2
    End Select
    
    'account for holidays within starting and ending dates
    NumHolidays = DCount("*", "tblHolidays", "[HolidayDate]>=#" & Format(StartDate, "yyyy\/mm\/dd") & "# And [HolidayDate]<=#" & EndDate & "# And Weekday([HolidayDate],2)<6")
        
    AddBusinessDays = EndDate + NumHolidays
    
End Function

Open in new window

Ron
Gustav BrockCIOCommented:
And so it continues. You also need to take account for holidays falling in a weekend. That's why I put the calculation method down and turned to evaluate the simple loop method with modern equipment. After adding the trick with the array it turned out to be very fast.

/gustav
IrogSintaCommented:
Gustav, that's what Graham already pointed out.  That's why I revised my DCount to account for holidays falling on weekends.  I mentioned this in my last post.

Ron
IrogSintaCommented:
Okay, I just noticed an error in my code, so this is the corrected version:
Public Function AddBusinessDays(startDate As Date, daysToAdd As Integer) As Date
    Dim numHolidays As Integer
    Dim endDate As Date
    
    'account for startDate falling on a weekend by subtracting 1 for falling on a Saturday, and 2 for a Sunday
    Select Case Weekday(startDate, vbMonday)
        Case 6
            startDate = startDate - 1
        Case 7
            startDate = startDate - 2
    End Select
    
    'calculate the ending date
    endDate = startDate + daysToAdd + Int((daysToAdd + (Weekday(startDate, vbMonday) - 1)) / 5) * 2

    'account for holidays within starting and ending dates
    numHolidays = DCount("*", "tblHolidays", "[HolidayDate]>=#" & Format(startDate, "yyyy\/mm\/dd") & "# And [HolidayDate]<=#" & endDate & "# And Weekday([HolidayDate],2)<6")
        
    AddBusinessDays = endDate + numHolidays
    
End Function

Open in new window

Ron
Gustav BrockCIOCommented:
Nice. Been there. I think you are approaching to see my point.
Gosh! It took me 15 years ...

/gustav
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.