calculating business day intervals

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
 
R

Rick B

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.
 
W

willie

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
 
G

Graham R Seach

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
---------------------------
 
Top