Results 1 to 9 of 9
  1. #1
    Radtastic10 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    65

    Calculating Workdays in between two dates


    I have found some solutions to this online. However, my problem is that these have all been for a typical 5 day work week.

    I have two criteria that I am not sure how to address in order for it to work how I would like.
    1. We don't work a typical work week. Every other week is a 4 day work week.
    2. I need to pull the dates from a table. Just wasn't sure how to enter those into the code using the correct syntax.

    https://msdn.microsoft.com/en-us/lib...ice.12%29.aspx

    This is the code that I was thinking about using as a guide to calculate the workdays.

    Suggestions on how to change it to fit my criteria

    Thanks ahead of time.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    If your workweek is odd,and only 4 days, then create a workday table.
    it would store all days the company did work.
    then it's easy to count.

  3. #3
    Radtastic10 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    65
    Ok. As far as the pulling the dates from the existing table,

    Is the syntax on my code correct?

    Code:
    Public Function Workdays(ByRef tbl.startDate As Date, _
         ByRef tbl.endDate As Date, _
         Optional ByRef strHolidays As String = "Holidays" _
         ) As Integer

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    See if this link is helpful.

    I would add the "non working day in the 4 day- week as a holiday in the holiday table"

  5. #5
    Radtastic10 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    65
    Okay, thanks. I was informed by the users not to worry about the 4 day work week. They said a normal 5 day week is fine. I tried calling the function in a query and it said "Undefined function 'Workdays' in expression.

    The function call: Working Days: Workdays([startDate], [endDate])

    When I complied the code it just highlighted the Weekdays function call in the code. But everything seems in order there.

    Ideas?

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I was informed by the users not to worry about the 4 day work week
    What sort of arithmetic is that?

  7. #7
    Radtastic10 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    65
    arithmetic for what? Confused by your question

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You want to count business days between two dates. Your work set up is a 5day week followed by a 4 day week.
    But it's OK to ignore/not worry about the 4 day week?

    So, the total of workdays between 2 dates = Number of weeks *( 5 days/ week) but ignore the 4 day week??

    What exactly does the "don't worry about the 4 day week mean"?

    Anyway, you know your environment better than the readers.

  9. #9
    Radtastic10 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    65
    We have decided that there is not a need to worry about the 4 day work week, and we can just consider all weeks the standard 5 day weeks. Because most of the time there is someone working on the day the extra day during the 4 day weeks.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Calculating dates
    By sivega in forum Queries
    Replies: 7
    Last Post: 06-14-2013, 08:18 PM
  2. Calculating turnaround time on workdays
    By mathonix in forum Queries
    Replies: 1
    Last Post: 04-22-2010, 05:34 PM
  3. Calculating Dates
    By TC0126 in forum Queries
    Replies: 1
    Last Post: 02-23-2010, 08:13 PM
  4. Calculating Dates
    By AJ0424 in forum Programming
    Replies: 1
    Last Post: 07-14-2009, 08:29 AM
  5. calculating due dates in access
    By trixxnixon in forum Forms
    Replies: 0
    Last Post: 09-28-2008, 12:35 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums