Results 1 to 9 of 9
Thread: Dates (A2000 SR1)

20021021, 09:01 #1
 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 HansThanks) 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 anyone done this before or provide some help ?
Regards
Dave

20021021, 10:36 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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)

20021021, 11:13 #3
 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

20021021, 11:38 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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?

20021021, 12:08 #5
 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

20021021, 12:39 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Dates (A2000 SR1)
Dave,
Did you copy the NextWorkday function from my first reply? The WorkdayAdd function needs it.

20021021, 13:20 #7
 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

20021021, 13:27 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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

20021021, 14:16 #9
 Join Date
 Jun 2002
 Location
 Cheadle, Staffordshire
 Posts
 2,177
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Dates (A2000 SR1)
Thanks Hans