Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Sep 2003
    Location
    Houston, Texas, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    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. #4
    Star Lounger
    Join Date
    Sep 2003
    Location
    Houston, Texas, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #6
    Star Lounger
    Join Date
    Sep 2003
    Location
    Houston, Texas, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •