Date Difference in days excluding Weekends and Holidays (1 Viewer)

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.
 

raskew

AWF VIP
Local time
Today, 17:56
Joined
Jun 2, 2001
Messages
2,734
Hi -

For starters, give this a try:

Code:
Public Function fGetWorkdays2(pstart As Date, pend As Date) As Integer
'************************************************
'Purpose:   Compute number of workdays (Mon - Fri)
'           between pStart and pEnd
're:        http://www.access-programmers.co.uk/forums/showthread.php?t=164254&highlight=simplified
'Coded by:  raskew
'Input:    ? fGetWorkdays2(#2/10/06#, #7/13/06#)
'Output:    110
'************************************************

   fGetWorkdays2 = 7 - WeekDay(pstart) + 5 * (DateDiff("ww", pstart, pend) - 1) + WeekDay(pend) - 1

End Function

HTH - Bob
 

Users who are viewing this thread

Top Bottom