Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Aug 2001
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculating Holidays and Workdays (97)

    Below is a copy of the Module I am currently using to calculate the total number of days between ReqDate and CompDate, excluding weekends. The end result is then put onto a report which shows how many items were completed (CompDate) in 1 day, 2 days, 3 days, etc...I cannot figure out how to insert some type of code into this existing module to also exclude weekends. I have a table called Holidays and the field name for the actual holiday is called Holidate. Does anyone have any experience with this? I've looked at the "Doing WorkDay Math in VBA" and can't make heads nor tails of it. I am not a programmer so I'm looking for something simple. Can anyone help me create some code?

    Function DateDiffW(ReqDate, CompDate)
    Const SUNDAY = 1
    Const SATURDAY = 7
    Dim NumWeeks As Integer

    If ReqDate > CompDate Then
    DateDiffW = 0

    Else
    Select Case WeekDay(ReqDate)
    Case SUNDAY: ReqDate = ReqDate + 1
    Case SATURDAY: ReqDate = ReqDate + 2
    End Select
    Select Case WeekDay(CompDate)
    Case SUNDAY: CompDate = CompDate - 2
    Case SATURDAY: CompDate = CompDate - 1
    End Select


    NumWeeks = DateDiff("ww", ReqDate, CompDate)
    DateDiffW = NumWeeks * 5 + WeekDay(CompDate) - WeekDay(ReqDate)
    End If
    End Function

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating Holidays and Workdays (97)

    ----------------
    I have a table called Holidays and the field name for the actual holiday is called Holidate
    ---------------------
    Why not identify your weekends in this table? The same way you identify holidays. That would keep it simple.

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Calculating Holidays and Workdays (97)

    First of all, you need to understand what the value returned by "ww" signifies. It DOESN'T mean the number of 7-day intervals! It stands for the difference between the week# of the first date and the week# of the 2nd date. So a 2-day date difference between Tuesday and Thursday is 0 weeks; but between Saturday and Monday is 1 week!

    So, you can really think of the # of weeks as the number of weekends. Your formula will fail because multiplies #Weeks by 5. You need to adjust this by comparing the day of the week of the 2 dates. Use the WordDay( ) function get the day of the week of each date, if the day of 2nd date is < day of 1st date, then a full week hasn't transpired.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Calculating Holidays and Workdays (97)

    After giving it more thought, I think you can use this function:
    <pre>public function gfWorkingDays (Beginning as Date, Ending as Date) as Integer
    'assumptions:
    ' Working days are M-F
    ' Day started and Day ended are counted a full days.
    ' tblHolidays contains only holidays that fall on M-F

    dim intDays as Integer

    if Beginning>Ending then
    gfWorkingDays=0
    exit function
    end if

    intDays = datediff("d",Beginning,Ending) + 1
    intDays = intDays - 2 * DateDiff("ww", Beginning, Ending)
    intDays = intDays + (Weekday(Beginning)=vbSunday)
    intDays = intDays + (Weekday(Ending)=vbSaturday)
    intDays = intDays - DCount("*","tblHolidays", _
    "Holiday BETWEEN " & Beginning & " AND " & Ending)
    gfWorkingDays = intDays
    end Function
    </pre>

    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating Holidays and Workdays (97)

    Mark has to add an assumption for his function: Beginning and Ending has to be in the same year.
    To avoid this, and avoid the holidays has to be M-F, try the following function.
    If your Holidays table in not attached, you can use a Seek instead of FindFirst to speed up the function.
    <pre>Function WorkDays(Beginning As Date, Ending As Date) As Integer
    Dim db As Database
    Dim rst As Recordset
    Dim LoopDay As Date
    Dim strCriteria As String
    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblHolidays", dbOpenDynaset)
    WorkDays = 0
    LoopDay = Beginning
    Do While LoopDay <= Ending
    If Weekday(LoopDay) <> vbSaturday And Weekday(LoopDay) <> vbSunday Then
    strCriteria = "holidate = #" & Format(LoopDay, "mm/dd/yyyy") & "#"
    rst.FindFirst strCriteria
    If rst.NoMatch = True Then
    WorkDays = WorkDays + 1
    End If
    End If
    LoopDay = LoopDay + 1
    Loop
    Set rst = Nothing
    Set db = Nothing
    End Function
    </pre>

    Up to you to choose between the two functions. Mark's function is much faster.

    Francois
    Francois

  6. #6
    New Lounger
    Join Date
    Aug 2001
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating Holidays and Workdays (97)

    Thanks you guys for you help!!! I'm OK using the FindFirst. I had to setup my master query so that it uses an expression to get to the Workdays function! I can't thank you enough!!!! <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

  7. #7
    Patty Austin
    Guest

    Re: Calculating Holidays and Workdays (97)

    I am needing to do the same thing. How did you setup your query to use an expression to get to the Workdays function?

    Thanks,

    Patty

  8. #8
    New Lounger
    Join Date
    Oct 2001
    Location
    Burlington, Washington
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating Holidays and Workdays (97)

    My only guess is to link your data to Excel and then run it through the workday function that accounts for both weekends and holidays. Then import the data back into Access. I am currently working on the same problem. Let me know if this helps

Posting Permissions

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