Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2006

    Question Unanswered: Help Using a Calculation for Workdays Between Two Dates

    Okay, I created a new module with the following code which is used to calculate workdays that exist between a start and end date contained in a table. I don't know if I need it in a module or not. I just need to calculate the number of workdays between two dates. It would be best if I didn't have to use a module because I am new to modules. According to this code, what do I need to name my table and fields in order to get this to work. Also, how do I get access to utilize the module? Where does the calculation get entered into my database? I need the calculated number of workdays to appear in a 3rd field of my table which is NumberWorkDays (1st two fields StartDate & EndDate, or whatever they are supposed to be named). Any help is greatly appreciated.

    Public Function GetWorkDays(dtStartDate As Date, dtEndDate As Date) As Integer
    Dim intDaysToSaturday As Integer
    Dim intDaysToSunday As Integer
    Dim intWeeks As Integer
    Dim intWorkDays As Integer
    Dim dtX As Date

    'Count work days from start date until Saturday of that week. intDaysToSaturday = vbSaturday - Weekday(dtStartDate, vbSunday) dtX = DateAdd("d", intDaysToSaturday + 1, dtStartDate) If intDaysToSaturday >= 5 Then intWorkDays = 5 Else intWorkDays = intDaysToSaturday
    End If

    'Count number of weeks between start date and end date, not counting 'the last week.intWeeks = DateDiff("ww", dtX, dtEndDate, vbSunday)

    'Count number of work days in the last week. intDaysToSunday = Weekday(dtEndDate, vbSunday) - vbSunday If intDaysToSunday >= 5 Then intWorkDays = intWorkDays + 5
    Else: intWorkDays = intWorkDays + intDaysToSunday
    End If

    'Multiply in all the weeks inbetween the first week and last week. intWorkDays = intWorkDays (intWeeks * 5)

    'Subtract out Holidays intWorkDays = intWorkDays - DCount("*", "Holidays", "HolidayDate >= #" & dtStartDate & _ "# AND HolidayDate <= #" & dtEndDate & "#") GetWorkDays = intWorkDays

    End Function

    I also know that the db needs a "Holidays" table with fields Holiday
    and HolidayDate.

  2. #2
    Join Date
    Dec 2002
    Préverenges, Switzerland
    don't store the calculated value in the table (see hundreds of posts here explaining why) unless you have a very good reason.

    for reports etc you can generate the days with a query using your function (i assume it works - did not check). for this to happen, the function must be public and in a module.

    your query would look something like
    SELECT [thisfield], [thatfield], GetWorkDays([startdateField], [enddateField]) As [WorkDays] FROM [sometable];


    LATER: added [] around all of them to avoid inadvertant suggestion that [] required for the function. if no spaces in the name you can drop the [] around it.

    LATER STILL: your function will not work as posted. you have comment lines running in to code lines:
    'Count work days from start date until Saturday of that week. intDaysToSaturday = vbSaturday blah blah
    needs to be
    'Count work days from start date until Saturday of that week.
    intDaysToSaturday = vbSaturday blah blah

    but this is probably just a posting artifact
    Last edited by izyrider; 10-27-06 at 12:45.
    currently using SS 2008R2

  3. #3
    Join Date
    Oct 2006
    Here is my goal. I have what is a "realtime" record of how much money is available for a few different projects. Salaries are paid roughly every 2 weeks or 10 business days.

    There is a "Project Assignment" table in which different personnel are assigned to different projects for varying periods of time. There are field names for "EmployeeName", "Project", "Start Date", "End Date", and "Wage Rate".

    I see how I can use a query to calculate the amount of money they will be paid for the period of the project.

    My main problem now is that I need a way to have the information from the "Project Assignment" table automatically generate a new Record in my "Expense" table for the pay periods twice a month. This dollar amount will be close to actual but does not have to be as I have an employee who manually updates the exact dollar amount for all expenses each week.

    I also have no clue on how to use a module. Thank you for your help.

  4. #4
    Join Date
    Oct 2006
    Maybe a better solution would be for me to find a way to apply thier monthly salary prorated for the days that they are assigned to the project. I still would need a way to have that amount automatically generate an expenditure on the 1st and 15th of each month.

  5. #5
    Join Date
    Oct 2006
    If you are looking for a similar solution please check the following link.

    Taurus is just great!

Posting Permissions

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