Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dates (A2000 SR1)

    Hello all .
    Heres my predicament.
    I've successfully used a module (Supplied by Hans-Thanks) for calculating working days:
    "basHolidayDatesWorkDays", which performs as it should, along side a table for holidays, and removes weekends.
    Now if its possible, I need to implement something along the same kind of way but with a twist.

    We calculate how long a vehicle should be in our workshop by the amount of estimated hours, doubled and divided by eight:-

    16 x 2=32, / 8 = 4 working days.
    If the book in date is Friday, then the ECD (Estimated Completion Date) 4 days, will be the following Wednesday, accounting for Sat & Sun not regarded as work days.

    I have the calculation sorted within a query, thats not a problem.
    I have the BookInDate.
    The ECD should start from the BookInDate.

    Has any-one done this before or provide some help ?

    Regards
    Dave

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dates (A2000 SR1)

    Here are two functions you might use. Below them more information.

    ' The NextWorkday function returns the first workday (not Saturday or Sunday) after aDate.

    Function NextWorkday(aDate As Date) As Date
    Dim d As Date
    d = aDate + 1
    Do While WeekDay(d) = vbSunday Or WeekDay(d) = vbSaturday
    d = d + 1
    Loop
    NextWorkday = d
    End Function

    ' The WorkdayAdd function returns the date aNumber workdays (not Saturday or Sunday) after aDate.
    ' Example: WorkdayAdd(#10/18/02#, 4) returns #10/24/02#.

    Function WorkdayAdd(aDate As Date, aNumber As Long) As Date
    Dim d As Date
    Dim i As Long
    d = aDate
    For i = 1 To aNumber
    d = NextWorkday(d)
    Next i
    WorkdayAdd = d
    End Function

    In your case, you have to decide first what to do if the estimated number of days is not a whole number. For example, if the estimated number of hours is 9, your calculation of days yields 9 * 2 / 8 = 2.25. Do you want to round this down to 2, or up to 3?

    If you want to round down, you can calculate the number of days as NumberOfHours 4. (The operator performs integer division)

    If you want to round up, you can use this Ceil function:

    Function Ceil(n As Double) As Long
    Ceil = Int(n) - (Int(n) <> n)
    End Function

    and calculate the number of days as Ceil(NumberOfHours / 4)

    When you have calculated the number of days from the number of hours, you must subtract 1, because you include the BookInDate in the number of days. In your example, the estimated number of days is 4, including Friday, so you must add 3 workdays to Friday, resulting in Wednesday.

    Combining all this, if you want to round down:

    ECD = WorkdayAdd(BookInDate, (NumberOfHours 4) - 1)

    or if you want to round up:

    ECD = WorkdayAdd(BookIndate, Ceil(NumberOfHours / 4) - 1)

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates (A2000 SR1)

    Hi Hans
    I didn't think this would be easy. (For me anyway) Here's what I've done.

    Copied the "WorkDayAdd" and inserted into the forms module.
    Copied the "Ceil" function the same, to the forms module.
    I've not alterred these in any way.

    Created an unbound text on the form and in the control source put the following:
    =WorkdayAdd([txtBookedIn],Ceil([ECD]/4)-1)

    txtBookedIn is a Dlookup field
    ECD is the calculation of amount of days, again a DLookup.

    I'm not if this was the correct procedure, but I get #Error? obviously something to do with the calculation.
    Thanks
    Dave
    Attached Images Attached Images

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dates (A2000 SR1)

    Dave,

    The functions I posted don't have checks for correctly handling null (empty) values and invalid entries, so if for instance, txtBookInDate doesn't contain a date, or an invalid entry, you'd get #Error.

    Also, your naming is confusing me - in the screenshot, ECD seems to be the number of days, while earlier, you stated that ECD = Estimated Completion Date. Is ECD the name of the control displaying the number of days?

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates (A2000 SR1)

    Hans
    Sorry if I confused the issue.
    ECD is the abbreviation for Estimated Completion Date, which the latter we never use.
    We all just say ECD.
    So my control is called ECD.
    In the screen shot, you can just about see the DLookup function which is:
    =DLookUp("ECD","qryECD","EstimateNo = " & [Forms]![frmDetails]![EstimateNo] & " and Supp = " & [Forms]![frmDetails]![supp])
    Thus returning the calculated number from the query.
    I created a calculating field in the query itself and just look it up from the ECD text on the form.

    If a date is entered I get Compile error. Sub or function not defined on the line containing:
    d = NextWorkday(d).

    I hadn't included the NextWorkday function in the forms module.
    Should I have done.

    Regards
    Dave

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dates (A2000 SR1)

    Dave,

    Did you copy the NextWorkday function from my first reply? The WorkdayAdd function needs it.

  7. #7
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates (A2000 SR1)

    I hadn't originally but have now, and it's working.

    This seems to do the trick;
    =WorkdayAdd([txtBookedIn],Ceil([ECD])-1)
    It returns the correct date, but as you said earlier 9*2/8=2.25
    Its not a problem, The calculation is correct.

    I altered this "=WorkdayAdd([txtBookedIn],Ceil([ECD])-1)" because an incorrect date was returned.
    See attached in blue bold text.

    Thanks once again Hans.
    p.s. How would I handle the Nulls ?

    Kindest Regards
    Dave
    Attached Images Attached Images

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dates (A2000 SR1)

    Dave,

    It is possible to modify the functions to return Null if one of the arguments is Null. This is done by making the arguments into Variants instead of Date etc. This means that the functions are slightly less efficient (handling of Variants incurs an overhead). Here are the modified versions:

    ' Returns the first workday after aDate

    Function NextWorkday(aDate As Variant)
    Dim d As Date
    If IsNull(aDate) Then
    NextWorkday = Null
    Exit Function
    End If
    d = DateValue(aDate) + 1
    Do While WeekDay(d) = vbSunday Or WeekDay(d) = vbSaturday
    d = d + 1
    Loop
    NextWorkday = d
    End Function

    ' Returns the date aNumber workdays after aDate

    Function WorkdayAdd(aDate As Variant, aNumber As Variant)
    If IsNull(aDate) Or IsNull(aNumber) Then
    WorkdayAdd = Null
    Exit Function
    End If
    Dim d As Date
    Dim i As Long
    d = DateValue(aDate)
    For i = 1 To aNumber
    d = NextWorkday(d)
    Next i
    WorkdayAdd = d
    End Function

  9. #9
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates (A2000 SR1)

    Thanks Hans

Posting Permissions

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