Solved

# Access 2010 VBA Function Calculate Workdays

Posted on 2014-12-02
929 Views
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
0
Question by:shieldsco
• 3
• 3

LVL 57

Expert Comment

ID: 40476783
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.
0

LVL 57

Expert Comment

ID: 40476791
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.
0

Author Comment

ID: 40476809
what would the code look like incorporated into the aforementioned code?
0

LVL 57

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 40476843
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.
0

Author Comment

ID: 40476896
Jim let me test
0

Author Closing Comment

ID: 40476993
Good Job Jim....Thanks
0