Counting of Business Days

  • Thread starter Thread starter news.gci.net
  • Start date Start date
N

news.gci.net

I have read on many forums of people having the same issue as myself, but no
one seems to have a solution or even a work around. i have multiple Excell
spreadsheets that calculate the number of business days, excluding holidays,
from the date we send an order to the date that we receive a reponse back
using the Networkdays function. Each speadsheet is begining to push the
limits of Excell and i would like to move the tracking to an Access
database, but i cannot get any function to work or macro to work the same
way as Networkdays. Can anyone provide help or coding that would help in my
search. Thank you.

Ryan
 
It is actually quite easy to write your own code to provide such a
function.

However, there is a ready working solution here:

http://www.mvps.org/access/datetime/date0006.htm

The above has two examples. The first example is just a simple number of
week days between two dates, and has no provisions for holidays.

The 2nd example is the above + holidays.
 
The following code, to return the number of business days between two dates,
was given to me by Douglas Steele MVP.

Public Function GetBusinessDays(dte1 As Date, dte2 As Date) As Integer
GetBusinessDays = DateDiff("d", dte1, dte2) - _
DateDiff("ww", dte1, dte2, 1) * 2 - _
IIf(Weekday(dte2, 1) = 7, _
IIf(Weekday(dte1, 1) = 7, 0, 1), _
IIf(Weekday(dte1, 1) = 7, -1, 0))
End Function

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Back
Top