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
LincAdminAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
You will need to implement the concept(s) in the links below to make this happen:

See these links:

http://support.microsoft.com/kb/210064

http://www.mvps.org/access/datetime/date0006.htm

mx

mx
0
CCastellanosCommented:
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.
0
Gustav BrockCIOCommented:
Use this function in your query:

Select *,
  ISO_WorkdayAdd([Start Date], [Number of Work Days]) As [End Date]
From
  tblYourTable;

/gustav
Public Function ISO_WorkdayAdd( _
  ByVal datDate As Date, _
  ByVal lngWorkdaysAdd As Long, _
  Optional ByVal bytWorkdaysOfWeek As Byte = 5) _
  As Date

' Purpose: Add number of working days to date datDate.
' Assumes: 1 to 7 working days per week.
'          First workday is Monday.
'          Weekend is up to and including Sunday.
' Limitation: Does not count for public holidays.
' May be freely used and distributed.
' 1999-04-23. Gustav Brock, Cactus Data ApS, Copenhagen
' 2000-10-03. Constants added.
'             Option for 5 or 6 working days per week added.
' 2002-01-10. Option for 1 to 7 working days per week added.
'             Allowed to add negative number of working days.
'             Adding of zero working days returns the next
'             working day if current day is not a working day.
  
' Test:
' For j = 0 To 10 : For i = 0 to 12 : ? j, i, ISO_WorkdayAdd(Date + j, i): Next i: Next j
 
  ' Minimum and maximum count of workdays per week.
  Const cbytWorkdaysCountMin  As Byte = 1
  Const cbytWorkdaysCountMax  As Byte = 7

  Dim bytMonday               As Byte
  Dim bytSunday               As Byte
  Dim intWeekdayFirst         As Integer
  Dim intWorkdayLast          As Integer
  Dim intDaysShift            As Integer
  Dim lngDays                 As Long
  Dim lngWeeks                As Long
  
  On Error GoTo Err_ISO_WorkdayAdd
  
  If bytWorkdaysOfWeek >= cbytWorkdaysCountMin And bytWorkdaysOfWeek <= cbytWorkdaysCountMax Then
    ' Find ISO weekday for Monday.
    bytMonday = WeekDay(vbMonday, vbMonday)
    ' Find ISO weekday for Sunday.
    bytSunday = WeekDay(vbSunday, vbMonday)
    ' Find ISO weekday for last workday.
    intWorkdayLast = bytMonday + bytWorkdaysOfWeek - 1
    
    ' Find ISO weekday for date datDate.
    intWeekdayFirst = WeekDay(datDate, vbMonday)
    ' Shift date datDate from weekend to Monday.
    If intWeekdayFirst > intWorkdayLast Then
      If lngWorkdaysAdd >= 0 Then
        datDate = DateAdd("d", bytSunday - intWeekdayFirst + 1, datDate)
      Else
        datDate = DateAdd("d", intWorkdayLast - intWeekdayFirst, datDate)
      End If
      ' Find ISO weekday for shifted date datDate.
      intWeekdayFirst = WeekDay(datDate, vbMonday)
    End If
    
    ' Calculate number of days date datDate shall be shifted.
    If lngWorkdaysAdd >= 0 Then
      ' Shift to proceeding Monday in current week.
      intDaysShift = intWeekdayFirst - bytMonday
    Else
      ' Shift to succeeding last workday in current week.
      intDaysShift = intWeekdayFirst - intWorkdayLast
    End If
    ' Shift date datDate.
    datDate = DateAdd("d", -intDaysShift, datDate)
    ' Calculate workdays to add from start/end of current work week.
    lngWorkdaysAdd = lngWorkdaysAdd + intDaysShift
    
    ' Calculate number of workweeks and additional workdays to add.
    lngWeeks = lngWorkdaysAdd \ bytWorkdaysOfWeek
    lngDays = lngWorkdaysAdd Mod bytWorkdaysOfWeek
    
    ' Add number of calendar weeks and additional calendar days to
    ' shifted date datDate.
    If lngWeeks <> 0 Then
      datDate = DateAdd("ww", lngWeeks, datDate)
    End If
    If lngDays <> 0 Then
      datDate = DateAdd("d", lngDays, datDate)
    End If
  End If
  
  ISO_WorkdayAdd = datDate
  
Exit_ISO_WorkdayAdd:
  Exit Function
  
Err_ISO_WorkdayAdd:
  ' Date datDate + lngWorkdaysAdd is outside date range of Access.
  ' Return time zero, 00:00:00.
  Resume Exit_ISO_WorkdayAdd

End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
What about holidays ?

mx
0
Gustav BrockCIOCommented:
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


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

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.