date functions available in Access (Access 2003)

Topic

New Reply

Is there a way to modify the dateadd function so that I can use it to add days, if the date falls on a Saturday or Sunday?

Reply | Quote

Replies

You’ll find a whole series of functions that handle workday arithmetic on the Access web: Date/Time: Doing WorkDay Math in VBA. If you copy those functions into a standard module in your database, you can use the functions in VBA code but also in expressions in queries, forms and reports.

Reply | Quote

Hans,

I have looked over the functions in the link that you provided, and do not see a specific one that would fit my need.

Reply | Quote

Ok, I have had some time to think about this. I had taken a some code from another post to help calculate workdays. When I looked at it for the one hundredth time, I finally saw were the code rounded down days to the previous workday instead of up to the next workday. I have made the modifecation and it works like it should. Modified code:

Public Function GetNextWorkday(ByVal StartDate As Date, _
ByVal lngInterval As Long) As Date

Dim lngWeeks As Long
Dim lngDays As Long

If lngInterval = 0 Then
GetNextWorkday = StartDate
ElseIf lngInterval > 0 Then
‘ Make sure StartDate is a workday (round up):
If Weekday(StartDate) = vbSunday Then
StartDate = StartDate + 1
ElseIf Weekday(StartDate) = vbSaturday Then
StartDate = StartDate + 2
End If

‘ Calculate lngWeeks and lngDays:
lngWeeks = lngInterval 5 ‘ Integer division operator
lngDays = lngInterval – (lngWeeks * 5) ‘ remainder
StartDate = StartDate + (lngWeeks * 7)
‘ Account for weekends:
If (DatePart(“w”, StartDate) + lngDays) > 6 Then
StartDate = StartDate + lngDays + 2
Else
StartDate = StartDate + lngDays
End If

Else ‘ lngInterval < 0
lngInterval = lngInterval * -1 ' Make positive & subtract later
' Make sure StartDate is a workday (round up):
If Weekday(StartDate) = vbSunday Then
StartDate = StartDate + 1
ElseIf Weekday(StartDate) = vbSaturday Then
StartDate = StartDate + 2
End If

lngWeeks = lngInterval 5
lngDays = lngInterval – (lngWeeks * 5)
StartDate = StartDate – (lngWeeks * 7)

If (DatePart("w", StartDate) – lngDays) < 2 Then
StartDate = StartDate – lngDays – 2
Else
StartDate = StartDate – lngDays
End If
End If

GetNextWorkday = StartDate

End Function

Reply | Quote