Disregard weekends in a date query

I have a query that counts the number of days taken to process a particular job in our facility and reports if a particular department has exceeded allotted time. I need this query to disregard weekends.  Currently I'm using criteria thats similar to this:
Query Field: Date()
Criteria: >[Date_Entered_Into_Dept] + 3
In other words report if the Current Date is 3 days or more later than the "Date_Entered_Into_Dept".
Any ideas how I can make this ignore weekends and holidays?

Who is Participating?
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
place the codes in a module.

to use in a query

select [Date_Entered_Into_Dept],getWorkdays([Date_Entered_Into_Dept],Date()) from tablex
where getWorkdays([Date_Entered_Into_Dept],Date())>3
Rey Obrero (Capricorn1)Commented:
you will need a function to do that and a table for holidays.

Function getWorkDays(vDate1 As Date, vDate2 As Date) As Long
Dim i As Long, dtStart
dtStart = vDate1
i = DateDiff("d", vDate1, vDate2) + 1
Do Until dtStart >= vDate2
    dtStart = dtStart + 1
    Do While Weekday(dtStart) = 1 Or Weekday(dtStart) = 7 _
        Or Not IsNull(DLookup("[Date]", "tbl_Holidays", "[Date]=#" _
             & dtStart & "#"))
            dtStart = dtStart + 1
            i = i - 1
getWorkDays = i

End Function
ckarrow2Author Commented:
I hate to be dense... but exactly where do I put this Function?
I understand the holiday table concept tho.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.