Calculate a date x business days before a given date no weekends no holidays Access

Would I be safe to say that tblHoliday merely includes a list of specific calendar dates deemed to be holidays (a table with a single date field?  - If so what is the name of the field).

HolidayDate.......I also have the name of the holiday in the table.

try this function

Function getDueDate(dDate As Date, Span As Integer) As Date
Dim j As Integer, i As Integer, dtStart
dtStart = dDate
For j = 1 To Span
    dtStart = dtStart + 1
    Do While Weekday(dtStart) = 1 Or Weekday(dtStart) = 7 _
        Or Not IsNull(DLookup("observedDate", "tblHolidays", "observedDate=#" _
     & dtStart & "#"))
        dtStart = dtStart + 1
        i = i + 1
    Loop
Next
getDueDate = DateAdd("d", (Span + i), dDate)

End Function

Continuing with capricorn1's approach I tend to prefer using Recordsets in liue of DLookup for performance reasons, (minor issue here if you're only dealing with one date but if you were running it against a query/table of dates (causing the function and dlookups to be run potentially thousands of times) the difference could be quite noticeable.

Function getDueDate(dat As Date, lngSpan As Long) As Date
    Dim lngDay as Long
    getDueDate = dat
    With CurrentDb.OpenRecordset("SELECT tblHoliday.HolidateDate FROM tblHoliday WHERE (tblHoliday.HolidateDate <= #" & dat & "#);", DAO.dbOpenSnapshot)
        Do
            If Not .EOF Then
                If .Fields(0).Value = getDueDate Then
                    .MoveNext
                Else
                    Select Case Weekday(getDueDate)
                    Case 2, 3, 4, 5, 6
                        lngDay = lngDay + 1
                    End Select
                End If
            Else
                Select Case Weekday(getDueDate)
                Case 2, 3, 4, 5, 6
                    lngDay = lngDay + 1
                End Select
            End If
            If lngDay = lngSpan Then Exit Do
            getDueDate = getDueDate - 1
        Loop
    End With
End Function

Select allOpen in new window

?? how many holidays are in a year..

Just noticed a couple issues with the code from my previous post, (Name of HolidayDate and the need to Sort the Recordset by Descending Date).

Also changed the name of the function to more accurately reflect what it is doing (finding a date X number of business days BEFORE a given date).  The other name suggests a date that would come after.

Function BusinessDaysBefore(dat As Date, lngSpan As Long) As Date
    Dim lngDay as Long
    BusinessDaysBefore = dat
    With CurrentDb.OpenRecordset("SELECT HolidayDate FROM tblHoliday WHERE (HolidayDate <= #" & dat & "#) ORDER BY HolidayDate DESC;", DAO.dbOpenSnapshot)
        Do
            If Not .EOF Then
                If .Fields(0).Value = BusinessDaysBefore Then
                    .MoveNext
                Else
                    Select Case Weekday(BusinessDaysBefore)
                    Case 2, 3, 4, 5, 6
                        lngDay = lngDay + 1
                    End Select
                End If
            Else
                Select Case Weekday(BusinessDaysBefore)
                Case 2, 3, 4, 5, 6
                    lngDay = lngDay + 1
                End Select
            End If
            If lngDay = lngSpan Then Exit Do
            BusinessDaysBefore = BusinessDaysBefore - 1
        Loop
    End With
End Function

Select allOpen in new window

General clarification.  If there is a startdate of July 11, 2008 which is a Friday, I want to see Monday, July 07, 2008 as the final date (no holidays and no weekend days).  If I use Monday, July 7, 2008 as the startdate, I want to see June 30, 2008 as the final date (allows for 2 weekend days and 1 holiday).

Capricorn1:  

Your rte gives me a date in advance not earlier than the start date as requested.  I tried putting a minus sign in front of the (Span + 1) in your line of code: getDueDate = DateAdd("d", (Span + i), dDate) , but still cannot get it to come out right....it gives me a date of 7/5/2008 which is a Saturday.

There are currently 8 holidays in the table.  However, I intend to add some more.

Rick Richards:

When I get to the following line of code:

With CurrentDb.OpenRecordset("SELECT tblHoliday.HolidateDate FROM tblHoliday WHERE (tblHoliday.HolidateDate <= #" & dat & "#);", DAO.dbOpenSnapshot)

I get an 'Runtime error 3061 Too few parameters. Expected 1.'  

hmm try this

Function getDueDate(dDate As Date, Span As Integer) As Date
Dim j As Integer, i As Integer, dtStart
dtStart = dDate
For j = 1 To Span
    dtStart = dtStart - 1
    Do While Weekday(dtStart) = 1 Or Weekday(dtStart) = 7 _
        Or Not IsNull(DLookup("observedDate", "tblHolidays", "observedDate=#" _
     & dtStart & "#"))
        dtStart = dtStart - 1
        i = i + 1
    Loop
Next
getDueDate = DateAdd("d", - (Span + i), dDate)

end function

can you in plain english, post the business rule that must be followed

The code I posted accounts for the issues you brought up in the general clarification.  It doesn't count the day provided, skips any holidays and weekends working it's way back the number of days provided in the span.

Regarding the error you received on the line...

With CurrentDb.OpenRecordset("SELECT tblHoliday.HolidateDate FROM tblHoliday WHERE (tblHoliday.HolidateDate <= #" & dat & "#);", DAO.dbOpenSnapshot)

... use the most recent version of code posted (it resolved the issue you're reporting caused by the misspelling of HolidayDate - notice that it says HolidateDate not HolidayDate).

With CurrentDb.OpenRecordset("SELECT HolidayDate FROM tblHoliday WHERE (HolidayDate <= #" & dat & "#) ORDER BY HolidayDate DESC;", DAO.dbOpenSnapshot)

I'll repost the entire snippet here just to keep it simple...

If you Run the following sample routine I think you'll find it is reporting the dates as requested...

Sub Sample()
    MsgBox "Four business days before 7/07/2008 is... " & BusinessDaysBefore(#07/07/2008#, 4)
    MsgBox "Four business days before 7/11/2008 is... " & BusinessDaysBefore(#07/11/2008#, 4)
End Sub

Capricorn1,

I believe WFBweb is trying to pass the function a date and then walk backwards through the calendar 4 days (not counting the day provided, omitting any weekends and any days included in tblHoliday.HolidayDate).  Thus any Friday would return the previous Monday, Thursday would return the previous Friday, Wednesday would return the previous Thursday and so on unless one of the calandar dates Monday-Friday happened to be a holiday in which case it would go back an additional day for every holiday encountered.  In the event that the holiday happened to fall on a Weekend then it's kind of mute since it would be omitted anyway although my code considers them to insure that as they are found the recordset advances to the next record (a recordset of Descending Dates).

Function BusinessDaysBefore(dat As Date, lngSpan As Long) As Date
    Dim lngDay as Long
    BusinessDaysBefore = dat
    With CurrentDb.OpenRecordset("SELECT HolidayDate FROM tblHoliday WHERE (HolidayDate <= #" & dat & "#) ORDER BY HolidayDate DESC;", DAO.dbOpenSnapshot)
        Do
            If Not .EOF Then
                If .Fields(0).Value = BusinessDaysBefore Then
                    .MoveNext
                Else
                    Select Case Weekday(BusinessDaysBefore)
                    Case 2, 3, 4, 5, 6
                        lngDay = lngDay + 1
                    End Select
                End If
            Else
                Select Case Weekday(BusinessDaysBefore)
                Case 2, 3, 4, 5, 6
                    lngDay = lngDay + 1
                End Select
            End If
            If lngDay = lngSpan Then Exit Do
            BusinessDaysBefore = BusinessDaysBefore - 1
        Loop
    End With
End Function

Select allOpen in new window

Sorry, one last code fix and I think we're there.

Try this version, strike the last.

Sub Sample()
    MsgBox "Four business days before 7/07/2008 is... " & BusinessDaysBefore(#7/7/2008#, 4)
    MsgBox "Four business days before 7/11/2008 is... " & BusinessDaysBefore(#7/11/2008#, 4)
End Sub
Function BusinessDaysBefore(dat As Date, lngSpan As Long) As Date
    Dim lngDay As Long
    BusinessDaysBefore = dat - 1
    Debug.Print "SELECT HolidayDate FROM tblHoliday WHERE (HolidayDate <= #" & dat & "#) ORDER BY HolidayDate DESC;"
    With CurrentDb.OpenRecordset("SELECT HolidayDate FROM tblHoliday WHERE (HolidayDate < #" & dat & "#) ORDER BY HolidayDate DESC;", DAO.dbOpenSnapshot)
        Do
            If Not .EOF Then
                If .Fields(0).Value = BusinessDaysBefore Then
                    .MoveNext
                Else
                    Select Case Weekday(BusinessDaysBefore)
                    Case 2, 3, 4, 5, 6
                        lngDay = lngDay + 1
                    End Select
                End If
            Else
                Select Case Weekday(BusinessDaysBefore)
                Case 2, 3, 4, 5, 6
                    lngDay = lngDay + 1
                End Select
            End If
            If lngDay = lngSpan Then Exit Do
            BusinessDaysBefore = BusinessDaysBefore - 1
        Loop
    End With
End Function

Select allOpen in new window

Argh.  Delete line 8 from the code above.  The Debug.Print line isn't needed (it won't break anything but it doesn't need to be there.)

looks like you want to get the previous monday date
try


Function GetMonDate(CurrentDate)
   If varType(CurrentDate) <> 7 Then
      GetMonDate = Null
   Else
      Select Case WeekDay(CurrentDate)
         Case 1       ' Sunday
            GetMonDate = CurrentDate + 1
         Case 2       ' Monday
            GetMonDate = CurrentDate - 7
         Case 3 To 7  ' Tuesday..Saturday
            GetMonDate = CurrentDate - WeekDay(CurrentDate) + 2
       End Select
   End If
End Function

capricorn1,

I'm not sure that's quite it.

1) Starting with the day provided, walk backwards through the calendar 4 days and...
    A) Don't count the day provided as one of the 4 calendar days (Skip over the day provided)
    B) Skip all Sunday's and Saturdays (move past them but don't count them as one of the 4 days)
    C) If one of the Day's Monday-Friday happens to be in tblHoliday.HolidayDate then skip past it, (Do not count it as one of the 4 days).

If provided 7/11/08  (A Friday) Return 7/7/08 (A Monday) - Note, Thursday, Wednesday, Tuesday and Monday (7/7/08) are the 4 days with 7/7/08 (A monday being the 4th day).
   -7/1/08 was not counted as that was the day provided.

If provided 7/7/08 (A Monday) Return 6/30/08 - Note 7/3/08, 7/2/08, 7/1/08 & 6/30/08 are the 4 days (Thursday, Wednesday, Tuesday and Monday respectively).
  - 7/7/08 was not counted as that was the day provided.  7/6/08 & 7/5/08 were not counted because that was Sunday & Saturday.  7/4/08 was not counted because that was a holiday.

Also note that we are counting backwards through the calendar not forward...

WFBweb,

Do I have it right?

Rick

typos..  Correction to last post

   -7/1/08 was not counted as that was the day provided.

should have read...

   -7/11/08 was not counted as that was the day provided.

I'm glad I was away from the pc doing aeobics walking for a while.  Now I'm going out to dinner.  When I get back I'll have at it again if there's time enough before The Closer.  (Now I know why I was having so much difficulty trying to develop the code myself!  And I  hadn't even thought of the situation where a holiday and a weekday are contiguous.)

The fact that weekends and holidays can overalap isn't actually so hard to deal with but it is one of the issues that merits consideration as it can impact what one has to do depending on ones approach.  Capricorns use of DLookup would allow one to simply disregard any holiday consideration if the day was determined to fall on a Saturday or Sunday - Using DLookup makes the case to check weekends first as that there's no need to look up anything from tblHoliday if the day is on a weekend.

The approach that I suggested, using a recordset instead, doesn't have this liberty as the recordset has to remain synchronized with the dates as each one is considered.   Although Using DLookup makes the code writing easier I remain enclined to do what it takes to use the Recordset instead since my own testing has reveled such a dramatic difference in performance.  An accurate side by side comparison is a little difficult since the two approach getting to the data in an entirely different way (DLookup Queries the Table each time you look the recordset taps it Only Once), the peformance difference is so dramatic that it doesn't much matter how you go about measuring how fast one is over the other since Dlookup tends to be about 80X slower.  In fairness, it makes little difference if you're just tapping the table a few times.  In your situation you'd tap it no less than 4 times and it is doubtful that you'd have to tap it more than 7.  Still not enough to make a difference when considering just one date.  It's when you run the function against a field in a query considering thousands of records that makes all the difference.  If you had to find the 4th prior business day for 10,000 records it's safe to say that DLookup would Tap the table approximately 50 to 60 thousand times, (the exact number would vary).  With the recordset you'd only tap it as many times as you had dates to consider, 10,000 records would mean 10,000 recordsets to build.

Either approach will benefit tremendously from the HolidayDate being Indexed.

Holiday.PNG

Rick Rickards
Doesn't seem to work when the date that is entered happens to be a holiday....otherwise ok.

How do you want it to respond if the date is a holiday?  

Ie:  If you entered July 4, 2008 what date would you want to see?  Currently it would return 6/30/08 (skipping the day entered then walking back through the 4 business days prior, (7/3, 7/2, 7/1 & 6/30 (Thr, Wed, Tue & Mon).  In this case the date entered happens to overlap with a holiday which is a similar issue to holidays overlapping weekends (though the code provided doesn't need to think of it as such).

Are you suggesting that you want a day other than 6/30/08 in this case?  If so what is the logic?  Something like, (If the day presented is a holiday step back through the calendar to the first non-weekend/Non-holiday date, disregard the first one and then start counting as previously explained?  As you might have noticed the code is only part of the battle and doomed to fail if the first step (a crystal clear objective that considers all anomalies) is clear.

WFBweb,

post the business rule that must be followed...

Capricorn.....I hate to show my ignorance, but what is a business rule?

Rick Rickards

Same logic as if the holiday was on the third or any other day of that week....back to June 27.  Note that if you put in 7/11 (a week that has no holiday) it would go back to Monday the 7th.  Ergo if you put in July 4 you'd not go back to the Monday but to the previous Friday because July 4th is a holiday.

Another example.  Currently If you put in Friday September 5th, the result is Friday August 29 because Labor Day is in that week.  August 29 is the date wanted.

Here you go.  Sounds like the logic is as I described but in anticipating of the other shoe about to drop I took on the assumption that if you provided the function 7/5/08 then you'd want to see 6/27/08 returned as well (same as if you gave it 7/4/08).  My guess is that you want to take any day provided, disregard it it if it a business day or if it and the days proceeding are weekends and/or holidays then disregard them until you get to the first business day which is also to be disregarded then count back 4 days according to same rational previously described.

Capricorn,

The business day rule is any day Monday through Friday that isn't a holiday (date found in tblHoliday).  

The logic from before was almost the way WFBweb wanted it but requires one more rule - Skip the day provided and days proceeding if they are holidays and then when you find your first business day skip it and then start counting.  Although WFBweb didn't say anything about it I strongly suspect the next shoe to drop is to apply the same rational to the day provided and days proceeding if it was a weekend AND/OR a holiday rather than just a holiday.  

Here's the logic being used - restated and moidified from what was previously posted

1) Starting with the day provided, walk backwards through the calendar 4 days and...
    A) Evaluate the day provided, if it is a holiday or weekend or both the date backwards until neither is the case.
         A1) One Condition 1A) has been met - or if it is already the case move the day back by 1 Then continue with logic in B & C
Don't count the day provided as one of the 4 calendar days (Skip over the day provided)
    B) Skip all Sunday's and Saturdays (move past them but don't count them as one of the 4 days)
    C) If one of the Day's Monday-Friday happens to be in tblHoliday.HolidayDate then skip past it, (Do not count it as one of the 4 days).

If provided 7/4/08 (A Monday) Return 6/27/08 - Skip 7/4/08 because it's a holiday, Skip 7/3/08 (First Business Day), Consider 7/2/08, 7/1/08, 6/30/08 (Wed, Tue & Mon), Skip 6/29 & 6/28 (Sun & Sat) and return 6/27/08 (The fourth business day that is not a holiday.

If provided 7/5/08 (A Monday) Return 6/27/08 - Skip 7/5/08 as it is a weekend, Skip 7/4/08 because it's a holiday, Skip 7/3/08 (First Business Day), Consider 7/2/08, 7/1/08, 6/30/08 (Wed, Tue & Mon), Skip 6/29 & 6/28 (Sun & Sat) and return 6/27/08 (The fourth business day that is not a holiday.

If provided 7/11/08  (A Friday) Return 7/7/08 (A Monday) - Note, Thursday, Wednesday, Tuesday and Monday (7/7/08) are the 4 days with 7/7/08 (A monday being the 4th day).
   -7/11/08 was not counted as that was the day provided.

If provided 7/7/08 (A Monday) Return 6/30/08 - Note 7/3/08, 7/2/08, 7/1/08 & 6/30/08 are the 4 days (Thursday, Wednesday, Tuesday and Monday respectively).
  - 7/7/08 was not counted as that was the day provided.  7/6/08 & 7/5/08 were not counted because that was Sunday & Saturday.  7/4/08 was not counted because that was a holiday.

Also note that we are counting backwards through the calendar not forward...

Sub Sample()
    MsgBox "Four business days before 7/04/2008 is... " & BusinessDaysBefore(#7/4/2008#, 4) 'Returns 6/37/08
    MsgBox "Four business days before 7/05/2008 is... " & BusinessDaysBefore(#7/5/2008#, 4) 'Returns 6/27/08
    MsgBox "Four business days before 7/07/2008 is... " & BusinessDaysBefore(#7/7/2008#, 4) 'Returns 6/30/08
    MsgBox "Four business days before 7/11/2008 is... " & BusinessDaysBefore(#7/11/2008#, 4) 'Returns 7/7/08
End Sub
 
Function BusinessDaysBefore(dat As Date, lngSpan As Long) As Date
    Dim lngDay As Long
    BusinessDaysBefore = dat
    With CurrentDb.OpenRecordset("SELECT HolidayDate FROM tblHoliday WHERE (HolidayDate <= #" & dat & "#) ORDER BY HolidayDate DESC;", DAO.dbOpenSnapshot)
        Do
            If .EOF Then
                Exit Do
            Else
                If .Fields(0).Value = BusinessDaysBefore Then
                    .MoveNext
                    BusinessDaysBefore = BusinessDaysBefore - 1
                Else
                    Select Case Weekday(BusinessDaysBefore)
                    Case 1, 7
                        BusinessDaysBefore = BusinessDaysBefore - 1
                    Case Else
                        Exit Do
                    End Select
                End If
            
            End If
        Loop
        BusinessDaysBefore = BusinessDaysBefore - 1
        Do
            If Not .EOF Then
                If .Fields(0).Value = BusinessDaysBefore Then
                    .MoveNext
                Else
                    Select Case Weekday(BusinessDaysBefore)
                    Case 2, 3, 4, 5, 6
                        lngDay = lngDay + 1
                    End Select
                End If
            Else
                Select Case Weekday(BusinessDaysBefore)
                Case 2, 3, 4, 5, 6
                    lngDay = lngDay + 1
                End Select
            End If
            If lngDay = lngSpan Then Exit Do
            BusinessDaysBefore = BusinessDaysBefore - 1
        Loop
    End With
End Function

Select allOpen in new window

7/4 holiday
7/3 1
7/2 2
7/1 3
6/30 4
6/29 weekend
6/28 weekend
6/27 5

7/11 1
7/10 2
7/9  3
7/8 4
7/7 5

7/23 1
7/22 2
7/21 3
7/20 weekend
7/19 weekend
7/18 4
7/17 5

9/5 1
9/4 2
9/3 3
9/2 4
9/1 holiday
8/31 weekend
8/30 weekend
8/29 5

Note: there are 4 business days previous to (and not including) the final date.  That is what I need.  However, I could also use day 4  as a result.  In the first case I'd use a 'greater than' in my query and in the second case, I'd use a 'greater than/equal.  Bottom line is I need to end up with all clients who have been 'in house' at least 4 days as of whatever date is chosen.

{ Bottom line is I need to end up with all clients who have been 'in house' at least 4 days as of whatever date is chosen. }

so the business rule is

{ Bottom line is I need to end up with all clients who have been 'in house' at least 4 days (excluding weekends and holidays )  as of whatever date is chosen. }

is that correct?

OK.  I see it.  The difference to the code is quite small (relocation of Line #30 from the previous example).

Sub Sample()
    MsgBox "Four business days before 7/04/2008 is... " & BusinessDaysBefore(#7/4/2008#, 4)  'Returns 6/30/08
    MsgBox "Four business days before 7/05/2008 is... " & BusinessDaysBefore(#7/5/2008#, 4)  'Returns 6/27/08
    MsgBox "Four business days before 7/07/2008 is... " & BusinessDaysBefore(#7/7/2008#, 4)  'Returns 6/30/08
    MsgBox "Four business days before 7/08/2008 is... " & BusinessDaysBefore(#7/8/2008#, 4)  'Returns 7/2/08
    MsgBox "Four business days before 7/11/2008 is... " & BusinessDaysBefore(#7/11/2008#, 4) 'Returns 7/8/08
    MsgBox "Four business days before 7/23/2008 is... " & BusinessDaysBefore(#7/23/2008#, 4) 'Returns 7/18/08
    MsgBox "Four business days before 9/5/2008 is... " & BusinessDaysBefore(#9/5/2008#, 4)   'Returns 9/5/08
End Sub
 
Function BusinessDaysBefore(dat As Date, lngSpan As Long) As Date
    Dim lngDay As Long
    BusinessDaysBefore = dat
    With CurrentDb.OpenRecordset("SELECT HolidayDate FROM tblHoliday WHERE (HolidayDate <= #" & dat & "#) ORDER BY HolidayDate DESC;", DAO.dbOpenSnapshot)
        Do
            If .EOF Then
                BusinessDaysBefore = BusinessDaysBefore - 1 
                Exit Do
            Else
                If .Fields(0).Value = BusinessDaysBefore Then
                    .MoveNext
                    BusinessDaysBefore = BusinessDaysBefore - 1
                Else
                    Select Case Weekday(BusinessDaysBefore)
                    Case 1, 7
                        BusinessDaysBefore = BusinessDaysBefore - 1
                    Case Else
                        Exit Do
                    End Select
                End If
            End If
        Loop
        
        Do
            If Not .EOF Then
                If .Fields(0).Value = BusinessDaysBefore Then
                    .MoveNext
                Else
                    Select Case Weekday(BusinessDaysBefore)
                    Case 2, 3, 4, 5, 6
                        lngDay = lngDay + 1
                    End Select
                End If
            Else
                Select Case Weekday(BusinessDaysBefore)
                Case 2, 3, 4, 5, 6
                    lngDay = lngDay + 1
                End Select
            End If
            If lngDay = lngSpan Then Exit Do
            BusinessDaysBefore = BusinessDaysBefore - 1
        Loop
    End With
End Function

Select allOpen in new window

capricorn.....I reasonably sure you can say that that is the business rule.  I've been trying to stay away from explaining exactly how I'm using this because, as you can see, I'm not all that good at explanations and I didn't want to spend time explaining the entire systems environment this is being used in.

Rick Rickards:

For some reason when 7/4 is processed the date matches that indicated below of 6/27, but when 7/11 is processed the resulting date is 7/8 not 7/7 as below.

7/4 holiday
7/3 1
7/2 2
7/1 3
6/30 4
6/29 weekend
6/28 weekend
6/27 5

7/11 1
7/10 2
7/9  3
7/8 4
7/7 5

Sounds like you want the dates that are listed as #5 from your Post ID:22113456

If that's the case then the code below will do that.

Function BusinessDaysBefore(dat As Date, lngSpan As Long) As Date
    Dim lngDay As Long
    BusinessDaysBefore = dat
    With CurrentDb.OpenRecordset("SELECT HolidayDate FROM tblHoliday WHERE (HolidayDate <= #" & dat & "#) ORDER BY HolidayDate DESC;", DAO.dbOpenSnapshot)
        Do
            If .EOF Then
                BusinessDaysBefore = BusinessDaysBefore - 1
                Exit Do
            Else
                If .Fields(0).Value = BusinessDaysBefore Then
                    .MoveNext
                    BusinessDaysBefore = BusinessDaysBefore - 1
                Else
                    Select Case Weekday(BusinessDaysBefore)
                    Case 1, 7
                        BusinessDaysBefore = BusinessDaysBefore - 1
                    Case Else
                        Exit Do
                    End Select
                End If
            End If
        Loop
        
        Do
            If Not .EOF Then
                If .Fields(0).Value = BusinessDaysBefore Then
                    .MoveNext
                Else
                    Select Case Weekday(BusinessDaysBefore)
                    Case 2, 3, 4, 5, 6
                        lngDay = lngDay + 1
                    End Select
                End If
            Else
                Select Case Weekday(BusinessDaysBefore)
                Case 2, 3, 4, 5, 6
                    lngDay = lngDay + 1
                End Select
            End If
            If lngDay = lngSpan + 1 Then Exit Do
            BusinessDaysBefore = BusinessDaysBefore - 1
        Loop
    End With
End Function

Select allOpen in new window

Rick Rickards:
For some reason when 7/4 is processed the final date is now of  6/26, not  6/27 as below.
7/4 holiday
7/3 1
7/2 2
7/1 3
6/30 4
6/29 weekend
6/28 weekend
6/27 5

I finally did it myself......not nearly as sophisticated as your code, but it works on every date I've tested on it so far.  And in the situation I'm using it speed is not a problem so DLookup is fine.

Public Function BusDaysBefore(pDate, Span As Integer)
Dim BusDays As Integer
Dim NonBusDays As Integer
Dim CheckDate As Date
CheckDate = pDate
Dim chkHolidays As Integer
Do Until BusDays = Span + 1
    If Weekday(CheckDate) = 1 Or Weekday(CheckDate) = 7 Then
        NonBusDays = NonBusDays + 1
        GoTo getnextrec
    End If
    If IsNull(DLookup("observedDate", "tblHolidays", "observedDate = #" & CheckDate & "#")) Then
        BusDays = BusDays + 1
        GoTo getnextrec
    End If
    chkHolidays = chkHolidays + 1
    NonBusDays = NonBusDays + 1
getnextrec:
CheckDate = CheckDate - 1
Loop
BusDays = Span
BusDaysBefore = DateAdd("d", -(BusDays + NonBusDays), pDate)
End Function

Might want to recheck the code (version) you're using.  Just rechecked my code from the last post ID: 22114014.  Given 7/4/08 it returns 6/27. ;-)

I just copied it down again and got the same result...6/26/08.  Can you think of anything that would cause the discrepancy?  I've got several entries in the tblHoliday, but can't see why that would cause a difference.

i used this to test:

MsgBox "Four business days before 7/4/2008 is... " & BusinessDaysBefore(#7/4/2008#, 4)

I can think of one thing that would cause the discrepancy.  Check tblHoliday to see if any dates between 7/3/08 and 7/26/08 are flagged as holidays.  I ran the test under the assumption that 7/4/08 was a holiday using a table setup as such.

This is the data in tblHoliday
HolidayDate      HolidayName
7/4/08      4th of July
9/1/08      Labor Day
11/11/08      Veteran'sDay
11/27/08      Thanksgiving
11/28/08      Day After Thanksgiving
12/25/08      Christmas
1/1/09      New Year's Day
1/19/09      Martin Luther King Day

I'll probably use Capricorn1's solution since it's easier for me to understand, but both work fine and if there was a response time problem, I'd probably choose Rick Rickard's solution.

At least you have options.  If this will ever happen to you try running each solution against a query of dates.  I think you'll find that if the table is big enough that you'll find one query makes you want and the other snaps to attention.  Also, the record set approach can be made to run even faster but no need to go there when we're still able to entertain the possibility that DLookup could be fast enough.