Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Calculate workdays and also exclude holidays

Posted on 2004-09-17
13
Medium Priority
?
853 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 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

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

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 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

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

721 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