# Thread: working days (Access 2000)

1. ## working days (Access 2000)

I need to calculate working days instead of days. Using the datediff function, I get correct answers for all days but need to narrow the calculation to working days only. Is there a way to do that?

Thanks for any help!!

deutsch

2. ## Re: working days (Access 2000)

See Calculate Number of Working Days for a simple function that ignores weekend days, and Doing WorkDay Math in VBA for a more complicated solution that also takes holidays into account. Both are on the Access Web.

3. ## Re: working days (Access 2000)

For another example of function to calculate workdays see this previous post:

<!post=Re: Date Spans (Access 2K) Post 274557 ,274557>Re: Date Spans (Access 2K) Post 274557 <!/post>

The code used is similar to that used in the Access Web example HansV provided link to, with an additional argument to count holidays if desired. A simpler approach for counting holidays is to use a table of Holiday dates to calculate the number of holidays in a given date range. For an example, see my reply in same thread as referenced above:

<!post=Re: Date Spans (Access 2K) Post 274209,274209>Re: Date Spans (Access 2K) Post 274209<!/post>

In this example the GetHolidayCount function simply opens a recordset using a Holidays table to get the holiday count, which can be subtracted from the number of days returned by a GetWorkdays type function. Or you could use the more convoluted approach provided in the Access Web "Doing WorkDay Math in VBA" example, which involves using an array for the holiday dates. I think using a holiday table is simpler because it would be easier to keep your Holidays table up to date than to figure out way to dynamically populate array used for this purpose - the holiday dates would still have to come from somewhere, you can't just hard-code them in...

HTH

4. ## Re: working days (Access 2000)

Thanks Hans,
This makes sense. However, how can I use this function in a query?
In the datediff function I can use the function in a query and calculate the days difference between two dates. Can I do the same with the function listed in the first code example you sent?

Thank you so much for your help!

deutsch

5. ## Re: working days (Access 2000)

Yes. Copy the code into a standard module (created by clicking 'New' in the Modules tab of the database window.). Say that you have fields DateStart and DateEnd in a table. In a query based on this table, you can create a calculated field

WorkDaysBetween: Work_Days([DateStart],[DateEnd])

6. ## Re: working days (Access 2000)

Thank you Hans, This worked like a charm.

deutsch

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•