Solved

Calculate workdays and also exclude holidays

Posted on 2004-09-17
13
849 Views
Last Modified: 2008-01-16
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
Comment
Question by:Hermanifer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +1
13 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 12089788
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 12089793
check this links too

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

by:Hermanifer
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 41

Expert Comment

by:shanesuebsahakarn
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

by:Rey Obrero (Capricorn1)
ID: 12090003

where are using the function above? Not in Access?

0
 

Author Comment

by:Hermanifer
ID: 12090007
I am using the above function in Access.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 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

by:shanesuebsahakarn
ID: 12090283
I wonder how you came up with that?
0
 
LVL 45

Expert Comment

by:aikimark
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

by:shanesuebsahakarn
shanesuebsahakarn earned 250 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

by:Hermanifer
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question