Solved

# Calculate workdays and also exclude holidays

Posted on 2004-09-17
842 Views
I am using the following code to calculate the number of workdays between two dates.  How can I modify this code to also exclude holidays (which are listed in a table)?

Function GetNetWorkDays(startDate As Date, endDate As Date) As Integer

Dim objFunction As MSOWCFLib.OCATP
Set objFunction = New MSOWCFLib.OCATP
GetNetWorkDays = objFunction.NetworkDays(startDate, endDate)
Set objFunction = Nothing
End Function
0
Question by:Hermanifer
• 4
• 3
• 3
• +1

LVL 119

Expert Comment

0

LVL 119

Expert Comment

How to Find Number of Working Days Between Two Dates
http://support.microsoft.com/?kbid=210562

HOW TO: Determine If a Date Falls on a Weekend or on a Holiday
http://support.microsoft.com/?kbid=210064
0

Author Comment

Thanks for the examples but I would like to modify the code that I am using to exclude the holidays.  Is there any way to define a variable that will exclude dates included on a Holdiay table?
0

LVL 41

Expert Comment

Reasonably straightforwards, if you already have a table of holidays. All you need to do is calculate the number of holidays between the start and end dates in your table, and deduct this from your total of days. I'm assuming your table is called tblHolidays and you have a field in it which is called HolidayDate, which holds (rather obviously) the date of the holiday.

You can get the number of holidays by using:
Dim numHols As Long

numHols=DCount("*","tblHolidays","HolidayDate>=#" & Format(startDate,"mm/dd/yyyy") & "# And HolidayDate<=#" & Format(endDate,"mm/dd/yyyy") & "#")

Now just deduct this from your total:
GetNetWorkDays=GetNetWorkDays-numHols

This assumes though that the holidays do not fall on weekends (which should be ok, on the assumption that if they do, a supplementary holiday day is provided).
0

LVL 119

Expert Comment

where are using the function above? Not in Access?

0

Author Comment

I am using the above function in Access.
0

LVL 119

Accepted Solution

Rey Obrero earned 250 total points

try this
change THolidays to name of your holiday table and HolidayDate to name of your holidays field

Function GetNetWorkDays(startDate As Date, endDate As Date) As Integer
Dim HolCount
HolCount = DCount("*", "THolidays", "HolidayDate Between #" & startDate & "# and  #" & endDate & "#")
Dim objFunction As MSOWCFLib.OCATP
Set objFunction = New MSOWCFLib.OCATP
GetNetWorkDays = objFunction.NetworkDays(startDate, endDate) - HolCount
Set objFunction = Nothing
End Function

0

LVL 41

Expert Comment

I wonder how you came up with that?
0

LVL 45

Expert Comment

You might also consider creating a query in your database that will return the number of holidays between two (parameter) dates:
Example
~~~~~~~~~
Select Count(*) As HolidayCount From Holidays
Where HolidayDate Between [parmStartDate] And [parmEndDate];
~~~~~~~~~

Then pass the values of the start and stop dates into a querydef variable before populating a recordset variable from the querydef.
Example:
Set qryHDays = currentdb.querydefs("HolidayDays")
qryHDays!parmStartDate = startDate
qryHDays!parmEndDate = endDate

Set rsHDays = qryHDays.OpernRecordset

Note: the number of holidays is available from:
rsHDays!HolidayCount

=============================
Note: Your application is dependent upon the selections made during Office installation.  MSOWCF is neither a Windows system DLL nor a MSAccess function.  You might consider replacing this with your own function.

When I did a similar function, I allowed the invoker of the function to specify whether the count of work days would include the (parameter) startdate or stopdate.  You can even be more clever by checking the two dates' time values and programmatically decide whether to include the date.

Note: date variables begin at midnight if no time value is present, so your enddate parameter might need adjusting, depending on your needs.
0

LVL 41

Assisted Solution

shanesuebsahakarn earned 250 total points
Hermanifer,

I posted the solution that capricorn1 used in his "answer" (see the fourth reply in this thread). Did you not see it?
0

Author Comment

I appologize for that.  I did not notice that this was the same.  Can I have the point split between the two of them?
0

## Featured Post

### Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…