Function to Add to Date excluding weekends
Hello!
Using Microsoft Access,
I would like to be able to enter two parameters:
1. Start Date
2. Number of Work Days
And use these to calculate the 'End Date' not including weekends. For example, using a 'Start Date' of 03/01/10, and a 'Work Days' of 12, this query would produce a result of 03/17/10. Thanks for the help!
John
Using Microsoft Access,
I would like to be able to enter two parameters:
1. Start Date
2. Number of Work Days
And use these to calculate the 'End Date' not including weekends. For example, using a 'Start Date' of 03/01/10, and a 'Work Days' of 12, this query would produce a result of 03/17/10. Thanks for the help!
John
If you want some steps so you can have fun creating the function yourself, here are some steps:
You will first identify the start date (StartDate)
Identify what day of the week is the StartDate.
Identify the number of days to count (DaysCount).
You can start an iteration from i=1 to DaysCount.
During the count add a Day to the StartDate and save as StartDateMod
Check if StartDateMod is weekend (W) (case, StartDate was Friday amd +1 is now Saturday)
If StartDateMod is W then add another DAY to StartDateMod all while i is still untouched.
Check if StartDateMod is still W (perhaps now Sunday) and if Yes, then add another DAY.
Now, StartDateMod is Monday
Add 1 to i
Run sequence again
This will run until i=DayCount.
You will first identify the start date (StartDate)
Identify what day of the week is the StartDate.
Identify the number of days to count (DaysCount).
You can start an iteration from i=1 to DaysCount.
During the count add a Day to the StartDate and save as StartDateMod
Check if StartDateMod is weekend (W) (case, StartDate was Friday amd +1 is now Saturday)
If StartDateMod is W then add another DAY to StartDateMod all while i is still untouched.
Check if StartDateMod is still W (perhaps now Sunday) and if Yes, then add another DAY.
Now, StartDateMod is Monday
Add 1 to i
Run sequence again
This will run until i=DayCount.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What about holidays ?
mx
mx
Those are just undisturbed workdays ...
Seriously, one method is to count - from a table or function - the number of holidays falling on workdays in the period in question, then add this count and count again until the found number of holidays is zero.
datDate2 = ISO_WorkdayAdd(datDate1, intWorkdays)
Do
intHolidays = CountHolidays(datDate1, datDate2)
If intHolidays > 0 Then
datDate2 = ISO_WorkdayAdd(datDate2, intHolidays)
End If
Loop Until intHolidays = 0
CountHolidays can be a simple function as shown.
/gustav
Seriously, one method is to count - from a table or function - the number of holidays falling on workdays in the period in question, then add this count and count again until the found number of holidays is zero.
datDate2 = ISO_WorkdayAdd(datDate1, intWorkdays)
Do
intHolidays = CountHolidays(datDate1, datDate2)
If intHolidays > 0 Then
datDate2 = ISO_WorkdayAdd(datDate2, intHolidays)
End If
Loop Until intHolidays = 0
CountHolidays can be a simple function as shown.
/gustav
Public Function CountHolidays( _
ByVal datDate1 As Date, _
ByVal datDate2 As Date) _
As Long
Const cstrTable As String = "tblHolidays"
Const cstrFormat As String = "yyyy\/mm\/dd"
Dim lngHolidays As Long
Dim strDate1 As String
Dim strDate2 As String
Dim strCriteria As String
strDate1 = Format(datDate1, cstrFormat)
strDate2 = Format(datDate2, cstrFormat)
strCriteria = "Holidate Between #" & strDate1 & "# And #" & strDate2 & "#"
lngHolidays = DCount("*", cstrTable, strCriteria)
CountHolidays = lngHolidays
End Function
See these links:
http://support.microsoft.com/kb/210064
http://www.mvps.org/access/datetime/date0006.htm
mx
mx