x
Solved

Calculate workdays and also exclude holidays

Posted on 2004-09-17
Medium Priority
861 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 120

Expert Comment

ID: 12089788
0

LVL 120

Expert Comment

ID: 12089793

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

ID: 12089879
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

ID: 12089966
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 120

Expert Comment

ID: 12090003

where are using the function above? Not in Access?

0

Author Comment

ID: 12090007
I am using the above function in Access.
0

LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 1000 total points
ID: 12090275

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

ID: 12090283
I wonder how you came up with that?
0

LVL 47

Expert Comment

ID: 12093935
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 1000 total points
ID: 12095813
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

ID: 12096521
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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.