dcollard23
Registered User.
- Local time
- Today, 17:56
- Joined
- Jun 15, 2009
- Messages
- 87
Hi, My first time on the forum.
I am trying to find the difference in days between Date Received and Target date but I dont know how to exclude weekends and holidays.
I have a holiday table and I have the following module used in another database that I imported into this database.
I have this expression in a query:
Expr1: DateDiff("d",[Date Received],[Target Date]) -
The first Module is named: ModNetWorkdays:
Function usbNetWorkdays(StartDate As Date, EndDate As Date, Optional NumofHolidays As Integer) As Integer
Dim retval As Integer
If StartDate > EndDate Then
Do While EndDate <= StartDate
If Weekday(EndDate) = 1 Or Weekday(EndDate) = 7 Then
EndDate = EndDate + 1
Else
retval = retval + 1
EndDate = EndDate + 1
End If
Loop
Else
Do While StartDate <= EndDate
If Weekday(StartDate) = 1 Or Weekday(StartDate) = 7 Then
StartDate = StartDate + 1
Else
retval = retval + 1
StartDate = StartDate + 1
End If
Loop
End If
usbNetWorkdays = retval - NumofHolidays
End Function
Function NumofHolidays(StartDate As Date, EndDate As Date) As Integer
Dim holcount As Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer
Dim retval As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("tblHolidays")
rs.MoveLast
holcount = rs.RecordCount
rs.MoveFirst
For i = 1 To holcount
If rs!holiday >= StartDate And rs!holiday <= EndDate Then
retval = retval + 1
Else
End If
rs.MoveNext
Next
NumofHolidays = retval
End Function
The Second Module is named: WorkDayDate VBA
Public Function WorkDayDate(TheDate As Date, ByVal DaysIncrement As Long, _
Optional IncludeHolidays As Boolean) As Variant
'*************************************************************
'Purpose: Returns date number of workdays in the
' past or future specified by DaysIncrement
'Arguments: TheDate - any valid date, or date field reference
' DaysIncrement - number of workdays from "TheDate"
' *Use negative number for past
'Returns: A Date or Null(if an error occured)
'*************************************************************
Const SUNDAY = 1
Const SATURDAY = 7
Dim EndDate As Date
Dim i As Long, j As Long
On Error GoTo err_WorkDayDate
If DaysIncrement < 0 Then
'go backwards (in the past)
i = -1
Else
'go forward (in the future date)
i = 1
End If
j = 1
EndDate = TheDate
Do Until j = Abs(DaysIncrement) 'number of workdays
If Weekday(EndDate) <> SUNDAY And Weekday(EndDate) <> SATURDAY Then
'since it is workday then check to see if it is a holiday
If IncludeHolidays = True Then
If IsNull(DLookup("Holiday", "tblHolidays", _
"[Holiday] = #" & EndDate & "#")) Then
'since it is not a holiday include another workday
j = j + 1
Else
End If
Else
'not worrying about holidays so include one more workday
j = j + 1
End If
Else
End If
EndDate = EndDate + i
Loop
WorkDayDate = EndDate
Exit Function
err_WorkDayDate:
WorkDayDate = Null
End Function
I don't know if I should use one of these or both and if so how do I do that?
Thanks in advance.
I am trying to find the difference in days between Date Received and Target date but I dont know how to exclude weekends and holidays.
I have a holiday table and I have the following module used in another database that I imported into this database.
I have this expression in a query:
Expr1: DateDiff("d",[Date Received],[Target Date]) -
The first Module is named: ModNetWorkdays:
Function usbNetWorkdays(StartDate As Date, EndDate As Date, Optional NumofHolidays As Integer) As Integer
Dim retval As Integer
If StartDate > EndDate Then
Do While EndDate <= StartDate
If Weekday(EndDate) = 1 Or Weekday(EndDate) = 7 Then
EndDate = EndDate + 1
Else
retval = retval + 1
EndDate = EndDate + 1
End If
Loop
Else
Do While StartDate <= EndDate
If Weekday(StartDate) = 1 Or Weekday(StartDate) = 7 Then
StartDate = StartDate + 1
Else
retval = retval + 1
StartDate = StartDate + 1
End If
Loop
End If
usbNetWorkdays = retval - NumofHolidays
End Function
Function NumofHolidays(StartDate As Date, EndDate As Date) As Integer
Dim holcount As Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer
Dim retval As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("tblHolidays")
rs.MoveLast
holcount = rs.RecordCount
rs.MoveFirst
For i = 1 To holcount
If rs!holiday >= StartDate And rs!holiday <= EndDate Then
retval = retval + 1
Else
End If
rs.MoveNext
Next
NumofHolidays = retval
End Function
The Second Module is named: WorkDayDate VBA
Public Function WorkDayDate(TheDate As Date, ByVal DaysIncrement As Long, _
Optional IncludeHolidays As Boolean) As Variant
'*************************************************************
'Purpose: Returns date number of workdays in the
' past or future specified by DaysIncrement
'Arguments: TheDate - any valid date, or date field reference
' DaysIncrement - number of workdays from "TheDate"
' *Use negative number for past
'Returns: A Date or Null(if an error occured)
'*************************************************************
Const SUNDAY = 1
Const SATURDAY = 7
Dim EndDate As Date
Dim i As Long, j As Long
On Error GoTo err_WorkDayDate
If DaysIncrement < 0 Then
'go backwards (in the past)
i = -1
Else
'go forward (in the future date)
i = 1
End If
j = 1
EndDate = TheDate
Do Until j = Abs(DaysIncrement) 'number of workdays
If Weekday(EndDate) <> SUNDAY And Weekday(EndDate) <> SATURDAY Then
'since it is workday then check to see if it is a holiday
If IncludeHolidays = True Then
If IsNull(DLookup("Holiday", "tblHolidays", _
"[Holiday] = #" & EndDate & "#")) Then
'since it is not a holiday include another workday
j = j + 1
Else
End If
Else
'not worrying about holidays so include one more workday
j = j + 1
End If
Else
End If
EndDate = EndDate + i
Loop
WorkDayDate = EndDate
Exit Function
err_WorkDayDate:
WorkDayDate = Null
End Function
I don't know if I should use one of these or both and if so how do I do that?
Thanks in advance.