Removing wk ends and holidays from dates

  • Thread starter Thread starter LG
  • Start date Start date
L

LG

I have 2 dates received and submitted. In order to arrive at a turn around
time I run a query and add a field of TAT:submitted-received. It than
returns the number of days. What I need is to take out from that number of
days the weekend and holdiays so I get the actual number of business days.
Any ideas what I need to do to get that to work. I know in excel it is
network days and a macro can be built for holidays but, not sure what to do
in access.
Thanks
 
There is more than one way to accomplish that, but it will require some
code.

You can automate the Excel function from Access VBA, for example.

Public Function MyExcelNetworkDays(ByVal StartDate As Date, ByVal EndDate As
Date)
MyExcelNetworkDays = Excel.WorksheetFunction.NetWorkDays(StartDate,
EndDate)
End Function

If you choose this option, you will need to set a reference to the Excel
Object Library.

Another option is described here.
http://www.mvps.org/access/datetime/date0012.htm

I believe you can Bing and turn up many other examples of the Access option,
but they all require construction of a holiday table.
 
LG said:
I have 2 dates received and submitted. In order to arrive at a turn around
time I run a query and add a field of TAT:submitted-received. It than
returns the number of days. What I need is to take out from that number
of
days the weekend and holdiays so I get the actual number of business days.
Any ideas what I need to do to get that to work. I know in excel it is
network days and a macro can be built for holidays but, not sure what to
do
in access.
Click to expand...


See these links:

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

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

Both include functions for calculating the number of work days between two
dates.
 
Dirk and George are correct. This will require a public function that
computes the number of days.

It will be something like:
TAT: GetTAT([submitted], [received])

It will also require a table that contains the dates of appropriate
holidays. That table will require some form of updating as the years pass or
for inserting special holidays. I can supply such a table that is based on
bank holidays.

The public function will have to check each date between the two dates. If
the date being checked is not a Saturday, Sunday or a holiday a variable will
be incremented. After each date is checked the variable is returned.

Jack Cannon
 
Back
Top