calculating business day intervals

  • Thread starter Thread starter mbmccoy
  • Start date Start date
M

mbmccoy

This has probably been asked and answered many times but here it is again.

How do I calculate the interval between two dates in Business days not
Calendar days. Accounting for weekends and holidays.

mbmccoy
 
This has been asked and answered many time. Please do a search. You can
FIND the answer out there rather than asking the qusetion again.
 
first create a table with holidate dates
second create this function
Public Function Holiday(dat1 As Variant, dat2 As Variant) As Integer

Dim DB As Database
Dim rst As DAO.Recordset
Dim strSQL As String
Set DB = CurrentDb()
Holiday = 0

strSQL = "select * from Holiday where holidate Between #" & dat1 _
& "# and #" & dat2 & "#"

If IsNull(dat1) Or IsNull(dat2) Then
Exit Function
End If
Set rst = DB.OpenRecordset(strSQL, dbOpenDynaset)

If rst.RecordCount > 0 Then
rst.MoveLast
Holiday = rst.RecordCount
End If

End Function

Third
create this function that will calculate the calendar dates
Public Function getbusinessdays(dte1 As Date, dte2 As Date) As Integer
getbusinessdays = DateDiff("d", dte1, dte2) - _
DateDiff("ww", dte1, dte2, 1) * 2 - _
IIf(Weekday(dte2, 1) = 7, _
IIf(Weekday(dte1, 1) = 7, 0, 1), _
IIf(Weekday(dte1, 1) = 7, -1, 0))
End Function

foufth steps
take the results of both funcitons and combine them."function
getbusinessdays - function Holiday
 
Willie,

getbusinessdays = DateDiff("d", dte1, dte2) - _
DateDiff("ww", dte1, dte2, 1) * 2 - _
IIf(Weekday(dte2, 1) = 7, _
IIf(Weekday(dte1, 1) = 7, 0, 1), _
IIf(Weekday(dte1, 1) = 7, -1, 0))

That code looks remarkably similar (in fact - identical) to code that was
developed by Douglas Steele MVP, and posted by me not 4 days ago in this
very group, and on 27th July in microsoft.public.access.

Just so you're aware of the etiquette of this and other newsgroups/websites,
it is polite to pay credit to the original author.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------
 
Back
Top