Calculating working days in Microsoft Access is a popular question, mostly because Microsoft's Help documentation on this subject is a little bit difficult to find. The term "Working days" usually means days that fall between Monday and Friday of any given week.
To calculate this, we can simply use a little bit of VBA code combined with the built-in DateDiff() function to help us prepare.
First, let's set our arguments for the function, which have to be the start date and end date that we want. We can do this in our first line of code:
Function Work_Days(BegDate As Variant, EndDate As Variant) As Long
Now for our function. The first thing we need to do is declare a few variables, so our code isn't unnecessarily complicated. Let's set variables for Total Weeks, a "Last Week" Indicator, and a simple "Remainder" to pick up the extra days that fall within the first and last weeks.
Dim WholeWeeks As Variant Dim DateCnt As Variant Dim EndDays As Integer
Now we need to get the values for these variables, so we can perform operations on them. Using the DateDiff() and DateAdd() functions, we can do this in two short steps:
WholeWeeks = DateDiff("w", BegDate, EndDate) DateCnt = DateAdd("ww", WholeWeeks, BegDate) EndDays = 0
Here's the tough part. Let's write a loop that works backwards, so we can calculate the number of extra days in the first and last weeks. This is necessary because, the function calculates total days by measuring the weeks between the two input dates. We need a small system to pick up that count of extra days that cannot be evaluated as one whole week. So, we can write the following loop to get the calculation:
Do While DateCnt <= EndDate If Format(DateCnt, "ddd") <> "Sun" And _ Format(DateCnt, "ddd") <> "Sat" Then EndDays = EndDays + 1 End If DateCnt = DateAdd("d", 1, DateCnt) Loop Work_Days = WholeWeeks * 5 + EndDays
Notice that we now have the end result. After running the loop, we can simply multiply the count of all other weeks by 5, since there are 5 working days in every week. As a final touch, we can add some error handling in case the user's input is not compatible with the Date data type, or in case of some other unexpected interruption:
Function Work_Days(BegDate As Variant, EndDate As Variant) As Long Dim WholeWeeks As Variant Dim DateCnt As Variant Dim EndDays As Integer On Error GoTo Err_Work_Days BegDate = DateValue(BegDate) EndDate = DateValue(EndDate) WholeWeeks = DateDiff("w", BegDate, EndDate) DateCnt = DateAdd("ww", WholeWeeks, BegDate) EndDays = 0 Do While DateCnt <= EndDate If Format(DateCnt, "ddd") <> "Sun" And _ Format(DateCnt, "ddd") <> "Sat" Then EndDays = EndDays + 1 End If DateCnt = DateAdd("d", 1, DateCnt) Loop Work_Days = WholeWeeks * 5 + EndDays Exit Function Err_Work_Days: If Err.Number = 94 Then Work_Days = 0 Exit Function Else MsgBox "Error " & Err.Number & ": " & Err.Description End If End Function
Below, we can see an example table, containing Beginning and End Dates.
We can calculate Working Days between two dates using the function and passing it the arguments, and calling this in a query as follows:
Below, you will see the results of the calculation:
And there you have it!
There are extensions to this code to also exclude holidays, but it is country specific, as different countries recognize different holidays. To add this functionality, the code will be much longer and more complex, as there is no easy way to calculate holidays in most scenarios.
If you care to attempt this, I have listed some of the holiday exclusions that apply to the United States as an example. Each block of code evaluates to an actual date of the holiday (TempDate variable) for any given year:
'MARTIN LUTHER KING DAY TempDate = IIf(Weekday("1/15/" & CStr(Year(Date))) = 2, "1/15/" & _ CStr(Year(Date)), IIf(Weekday( _ "1/16/" & CStr(Year(Date))) = 2, "1/16/" & CStr(ctr2) _ , IIf(Weekday("1/17/" & _ CStr(Year(Date))) = 2, "1/17/" & CStr(Year(Date)), IIf(Weekday _ ("1/18/" & CStr(Year(Date))) = _ 2, "1/18/" & CStr(Year(Date)), IIf(Weekday("1/19/" & CStr _ (Year(Date))) = 2, "1/19/" & _ CStr(Year(Date)), IIf(Weekday("1/20/" & CStr(Year(Date))) = 2 _ , "1/20/" & CStr(Year(Date)), IIf _ (Weekday("1/21/" & CStr(Year(Date))) = 2, "1/21/" & CStr _ (Year(Date)), 0)))) _ ))) 'MEMORIAL Day TempDate = IIf(Weekday("5/31/" & CStr(Year(Date))) = 2, "5/31/" & _ CStr(Year(Date)), IIf(Weekday( _ "5/30/" & CStr(Year(Date))) = 2, "5/30/" & CStr(ctr2) _ , IIf(Weekday("5/29/" & _ CStr(Year(Date))) = 2, "5/29/" & CStr(Year(Date)), IIf(Weekday _ ("5/28/" & CStr(Year(Date))) = _ 2, "5/28/" & CStr(Year(Date)), IIf(Weekday("5/27/" & CStr _ (Year(Date))) = 2, "5/27/" & _ CStr(Year(Date)), IIf(Weekday("5/26/" & CStr(Year(Date))) = 2 _ , "5/26/" & CStr(Year(Date)), IIf _ (Weekday("5/25/" & CStr(Year(Date))) = 2, "5/25/" & CStr _ (Year(Date)), 0)))) _ ))) 'LABOR Day TempDate = IIf(Weekday("9/1/" & CStr(Year(Date))) = 2, CStr( _ Year(Date)), IIf(Weekday("9/2/" & _ CStr(Year(Date))) = 2, "9/2/" & CStr(Year(Date)), IIf(Weekday _ ("9/3/" & CStr(Year(Date))) = _ 2, "9/3/" & CStr(Year(Date)), IIf(Weekday("9/4/" & CStr _ (Year(Date))) = 2, "9/4/" & _ CStr(Year(Date)), IIf(Weekday("9/5/" & CStr(Year(Date))) = 2 _ , "9/5/" & CStr(Year(Date)), IIf _ (Weekday("9/6/" & CStr(Year(Date))) = 2, "9/6/" & CStr _ (Year(Date)), IIf(Weekday("9/7/" _ & CStr(Year(Date))) = 2, "9/7/" & CStr(Year(Date)), 0) _ )))))) 'THANKSGIVING Day TempDate = IIf(Weekday("11/22/" & CStr(Year(Date))) = 5, "11/22/" & _ CStr(Year(Date)), IIf(Weekday( _ "11/23/" & CStr(Year(Date))) = 5, "11/23/" & CStr(ctr2) _ , IIf(Weekday("11/24/" & _ CStr(Year(Date))) = 5, "11/24/" & CStr(Year(Date)), IIf(Weekday _ ("11/25/" & CStr(Year(Date))) = _ 5, "11/25/" & CStr(Year(Date)), IIf(Weekday("11/26/" & CStr _ (Year(Date))) = 5, "11/26/" & _ CStr(Year(Date)), IIf(Weekday("11/27/" & CStr(Year(Date))) = 5 _ , "11/27/" & CStr(Year(Date)), IIf _ (Weekday("11/28/" & CStr(Year(Date))) = 5, "11/28/" & CStr _ (Year(Date)), 0)))) _ )))