# Access 2010 VBA Function Calculate Workdays

I'm using the code below to calculate workdays and I'm try to figure out how to incorporate holidays in the function. Any suggestions??

Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
EndDays = 0
Do While DateCnt < EndDate
If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
Loop
Work_Days = WholeWeeks * 5 + EndDays
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

President / OwnerCommented:
You need to add a table of holidays, then check the dates as you add if it's a holiday or not.  Only way to do it.

You can optimize a bit; get a recordset of holidays between start and end, sorted in ascending sequence.  Then check on each iteration of the loop, and if = skip and fetch the next holiday record.

Jim.
President / OwnerCommented:
the other way to do it of course is to simply count the number of holidays between start and end and subtract at the end.

Jim.
Author Commented:
what would the code look like incorporated into the aforementioned code?
President / OwnerCommented:
Work_Days = WholeWeeks * 5 + EndDays  - Dcount("*","tblHolidays","[HolidayDate] Between #" & Format\$(BegDate,"MM/DD/YY")  & "#  AND #" & Format\$(EndDate,"MM/DD/YY") & "#")

assuming tblHolidays only contains dates that are not Saturday or Sunday.

Jim.

Experts Exchange Solution brought to you by