Solved

Calculate workdays and also exclude holidays

Posted on 2004-09-17
13
845 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
  • 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

770 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