Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Oct 2001
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Workday Hours (A97)

    I have two date/time fields, the user pushes a button which populates these fields based on date and time of the machine. The department promises a response within 8 hours. I need to compute the difference which excludes weekends, holidays, and hours between 6p.m. and 7a.m. to derive the number of hours. I have read/seen many of the functions that exclude weekends and holidays. It would be no problem to setup a table to list the holidays. How can I incorporate all of this to only get a response back utilizing workday hours.

  2. #2
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workday Hours (A97)

    I think this code should help you with your query.
    You may need to hack the date format depending where you live ( It works in the UK <img src=/S/smile.gif border=0 alt=smile width=15 height=15> )

    Function funCompleteTime(dateIn As Date) As Date
    Const intHourStart As Integer = 7
    Const intHourEnd As Integer = 18
    Const intTargetTime As Integer = 8

    Dim dblRemainingTime As Double
    Dim intAddDays As Integer
    Dim dateCheck As Date
    Dim fDone As Boolean

    If Hour(dateIn) > intHourEnd - intTargetTime Then
    intAddDays = 1
    Do Until fDone = True
    dateCheck = dateIn + intAddDays
    If WeekDay(dateCheck) = vbSaturday Then
    intAddDays = intAddDays + 1
    ElseIf WeekDay(dateCheck) = vbSunday Then
    intAddDays = intAddDays + 1
    ElseIf DLookup("[dateHoliday]", "tblHolidayList", "[dateHoliday] = #" & Format(dateCheck, "dd/mm/yyyy") & "#") Then
    intAddDays = intAddDays + 1
    Else
    fDone = True
    End If
    Loop
    dblRemainingTime = intTargetTime - (intHourEnd - ((dateIn - Int(dateIn)) * 24))
    funCompleteTime = Int(dateIn) + intAddDays + (intHourStart + dblRemainingTime) / 24
    Else
    funCompleteTime = dateIn + intTargetTime / 24
    End If
    End Function


    Peter

  3. #3
    New Lounger
    Join Date
    Oct 2001
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workday Hours (A97)

    Thanks for the quick response, I'll give it a try! I had trouble getting on the system so I couldn't thank you sooner!

Posting Permissions

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