Deducting days from a date

[richardmorris0]

I am new to Access programming and I am trying to update an access date field QCDay in a form frmProductionControlInput so that on focus it brings in a date which is 5 working days excluding holidays and weekends less than another field DeliveryDay in the same form. I have created a tblHolidays table with HolidayDate as the field containing the holidays. Can anyone help. Thanks Richard

[Welshgasman]

Always my first point of call.
https://www.google.com/search?q=calc...t=gws-wiz-serp

and
https://www.google.com/search?q=subt...hrome&ie=UTF-8

[June7]

Need VBA procedure. Calculating a date value excluding holidays and weekends is a common topic. Google it. Here is one discussion https://www.accessforums.net/showthread.php?t=60206

[madpiet]

Arvin Meyer (former Access MVP) did this like 25 years ago. (even more now!) But he used VBA, because you have to loop over a table of dates and exclude holidays and weekends. It gets messy. Might be on mvps.org/access or datastrat.com

[señor_sriracha]

I had created a method that used recursion but it was only designed to skip weekends. It steps forward (or back) one day then checks that date to see if it's a Sat or Sun. If not, it decrements the loop counter, if it is then it doesn't change the loop counter and then recalls itself. You could probably add a DLOOKUP to your holidays table to the check for a Sat or Sun. The only drawback to it was the number of days it could look into the future (or past). It depends how much RAM you have, but it was a high enough number that I didn't think I would exceed it in practical use. I can't remember exactly, but I think it was over a year, maybe two or three.

[madpiet]

I am new to Access programming and I am trying to update an access date field QCDay in a form frmProductionControlInput so that on focus it brings in a date which is 5 working days excluding holidays and weekends less than another field DeliveryDay in the same form. I have created a tblHolidays table with HolidayDate as the field containing the holidays. Can anyone help. Thanks Richard

Twenty-odd years ago, Arvin Meyer wrote some code that does that. you can find it here.

[señor_sriracha]

I had created a method that used recursion but it was only designed to skip weekends.

Et voilà:

' recursive function to add (or subtract) business days to a date
' business days exclude Saturdays and Sundays (holidays not accounted for)
' limit 2185 days before out of stack space error
Public Function BusinessDaysFrom(ByVal dtStart As Date, days As Integer) As Date
    Dim dayNum As Integer
    Dim nextDay As Date


    On Error GoTo bdError


    ' base case
    If days = 0 Then
        BusinessDaysFrom = dtStart
        Exit Function
    End If


    ' add (or subtract) a day to the date argument
    nextDay = dtStart + IIf(days > 0, 1, -1)


    dayNum = Weekday(nextDay)


    ' check to see if day plus (or minus) one is a saturday(7) or sunday(1)
    ' only decrement (or increment) the days counter if it's a weekday
    If dayNum <> vbSunday And dayNum <> vbSaturday Then
        days = days - IIf(days > 0, 1, -1)
    End If


    ' call next frame
    BusinessDaysFrom = BusinessDaysFrom(nextDay, days)


exitBD:
    Exit Function


bdError:
    MsgBox "Error calculating next business day" & vbCrLf & _
            vbCrLf & Err.Description, vbCritical + vbOKOnly, "Recursion Error"
    Resume exitBD


End Function