Tuesday Holiday business day count error

[kjpreston]

Hello all!
I have a function written by someone else that has worked for years, except when a holiday falls on a Tuesday.
Here it is:
Public Function busydays(b As Date, e As Date) As Integer
Dim x As Integer
Dim y As Integer
Dim w As Date
Dim h As Date
w = DateAdd("d", 2, e)
h = DateAdd("d", -1, e)
y = DateDiff("d", b, DateAdd("d", 1, e))
x = DateDiff("ww", b, e, vbSunday)
x = x + DateDiff("ww", b, e, vbSaturday)
x = x + DCount("[HDate]", "[Holiday]", "[Hdate] > #" & b & "# and [Hdate] < #" & w & "#")
busydays = y - x
End Function
If you run this in the intermediate window and set b as #3/7/14# and e as #3/10/14# you get 2 days which is correct. However if you set b as #11/7/14# and e as #11/10/14# you get 0. I have no idea how to fix this. I have a report that uses this function and it is do by the end of the week.
Can any of you help me?

 

[MichaelRed]

There are numerous FAQs re date calculations. The better ones utilize a table of HOLIDAYS. Please look up one (and use) or more of these. Many experienced programmers utilize a comprehensive date table which include one or more holidays fields to accommodate the difference in the celebrated holidays for different segments of the populations. There is some literature supporting this approach, which could be useful for your review.



MichaelRed

 

[SkipVought]

In my experience at 4 aerospace companies, they ALL use a corporate calendar/manufacturing/accounting date table. No ambiguity! EVERYONE uses the same table. No ambiguity!