Solved

# Function to Add to Date excluding weekends

Posted on 2010-03-29
Medium Priority
1,462 Views
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
0
• 2
• 2

LVL 75

Expert Comment

ID: 29024626
You will need to implement the concept(s) in the links below to make this happen:

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

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

mx

mx
0

LVL 1

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
Run sequence again
This will run until i=DayCount.
0

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]
From
tblYourTable;

/gustav
``````Public Function ISO_WorkdayAdd( _
ByVal datDate As Date, _
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
'             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

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
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.
' 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.
' Calculate workdays to add from start/end of current work week.

' shifted date datDate.
If lngWeeks <> 0 Then
End If
If lngDays <> 0 Then
End If
End If

Exit Function

' Date datDate + lngWorkdaysAdd is outside date range of Access.
' Return time zero, 00:00:00.

End Function
``````
0

LVL 75

Expert Comment

ID: 29071177

mx
0

LVL 53

Expert Comment

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.

Do
intHolidays = CountHolidays(datDate1, datDate2)
If intHolidays > 0 Then
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
``````
0

## Featured Post

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.