databasedev.co.uk - database solutions and downloads for microsoft access

Microsoft Access VBA

Calculating Working Days in Microsoft Access

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

Working Example

Below, we can see an example table, containing Beginning and End Dates.

Table of data

We can calculate Working Days between two dates using the function and passing it the arguments, and calling this in a query as follows:

Design of the query, showing the function and the arguments

Below, you will see the results of the calculation:

Results of the Working Days 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)))) _
               )))

 

The Author

Adam Evanovich lives in Iowa in the United States and frequently works on contract in various industries. He started using Access in 1997 to record notes in a small database for a marketing program. Since then he has continued to explore the models that are available in Access and often uses them to streamline various small business operations.

Adam's database skills also include MySQL, Oracle, and SQL Server implementations. He also enjoys spending some of his free time understanding the history of technology. In his off hours he enjoys exercising, spending time friends and family and exploring the mountains and forestry.

If you'd like to contact Adam, you can reach him through his email address: the.net.2.0.0.0@gmail.com