Function to Add to Date excluding weekends

Posted on 2010-03-29
Medium Priority
Last Modified: 2012-06-21

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!

Question by:LincAdmin
  • 2
  • 2
LVL 75
ID: 29024626
You will need to implement the concept(s) in the links below to make this happen:

See these links:





Expert Comment

ID: 29061824
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.
LVL 53

Accepted Solution

Gustav Brock earned 2000 total points
ID: 29068092
Use this function in your query:

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

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)
        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
      ' 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 Function
  ' 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

LVL 75
ID: 29071177
What about holidays ?

LVL 53

Expert Comment

by:Gustav Brock
ID: 29073958
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)
  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.


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


Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question