Excluding Weekends and Holidays

[jamphan]

Is there a way to exclude weekends and holidays from a turnaround time formula? If I have a start date and an end date, I do not want the turnaround time to be skewed when people are not here. Thanks for the help.

 

[krberube]

I got this off this board a while ago and it works for business days, but does NOT exclude holidays. maybe you can start with this.?

make a module with the following code:

Option Compare Database

'Function to calculate business days, does NOT consider holidays!
Function DateDiffW(BegDate, EndDate)
Const SUNDAY = 1
Const SATURDAY = 7
Dim NumWeeks As Integer

If IsDate(BegDate) = False Then Exit Function
If IsDate(EndDate) = False Then Exit Function

If BegDate > EndDate Then
DateDiffW = 0
Else
Select Case Weekday(BegDate)
Case SUNDAY: BegDate = BegDate + 1
Case SATURDAY: BegDate = BegDate + 2
End Select
Select Case Weekday(EndDate)
Case SUNDAY: EndDate = EndDate - 2
Case SATURDAY: EndDate = EndDate - 1
End Select
NumWeeks = DateDiff("ww", BegDate, EndDate)
DateDiffW = NumWeeks * 5 + Weekday(EndDate) - Weekday(BegDate)
End If
End Function

Then call the module like:
=DateDiffW([yourfield beginDate],[yourfield EndDate])

 

[ColinEssex]

You'll need a table for the holiday dates. Do a search, this has been covered many times.

Col