Counting the Number of Working Days in Access 2007

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Summary:  Learn how to calculate the number of working days between two dates in Microsoft Office Access 2007.

Sal Ricciardi, Microsoft Corporation

January 2009

Applies to: 2007 Microsoft Office system, Microsoft Office Access 2007

Contents

• Overview

• Working Days Defined

• Dates and Times in Access 2007

• Calculating Working Days

• About the DateDiff and DatePart Built-in Functions

• Creating User-Defined Functions

• Using the Workdays Function in a Query

• Using the Workdays Function in a Form or Report

• Finding the Next, Previous, First, or Last Workday in the Month

• Conclusion

• Download the Sample Database

• Additional Resources

Overview

In your business, you may have to calculate the number of working days between two dates. Microsoft Office Access 2007 and its programming language, Visual Basic for Applications (VBA), are designed to help you easily write a function to perform this calculation. This article describes the Weekdays and Workdays user-defined functions, and explains how to use these functions in your database applications. Specifically, you can use the Workdays user-defined function to count the number of working days between two dates. This article also includes a link to a book excerpt that describes how to find the next, previous, first, or last workday in the month.

Working Days Defined

In the United States, a typical five-day work week begins on Monday and ends on Friday. These five weekdays are usually working days, except for holidays that occur on a weekday. Holidays are non-working days. Thus, a working day is defined as a weekday that is not a holiday. The weekly recurring non-working days are the first day of the week, Sunday, and the last day of the week, Saturday. These two days are otherwise known as weekend days.

You might use a different work week and use different weekend days. If so, configure the functions that are provided to suit your weekdays.

Dates and Times in Access 2007

You track dates and times in Access using the date/time data type. The date/time data type is a fundamental data type that you can use to define the data type for a column in a table. You use it to store calendar dates and times to use with the rich feature set in Access. Access can perform date arithmetic calculations and, for example, you can calculate the number of days that have elapsed since an invoice date to determine the age of accounts receivable. Access includes a date-validation feature so a user cannot enter an invalid date like 2/31.

Access stores a date/time value internally as a double-precision floating-point number, often known as a serial number. Each such number contains both a date component and a time component. The integer portion that is displayed to the left of the decimal point represents the date. The fractional portion that is displayed to the right side of the decimal point represents the time. Figure 1 shows a conceptual diagram of the date.time serial number.

Figure 1. Conceptual diagram of date.time serial number

The double-precision number in Figure 1 represents December 24th, 2008, at 9:00 P.M. because Access stores the number of days elapsed since the base date of 12/30/1899 as the date component. There are 39,806 days from the base date 12/30/1899 to December 24th, 2008.

Negative values in the date component represent dates earlier than the base date. For example, a -1 as the date component means one day less than the base date, or 12/29/1899.

The time component is a fraction of a twenty-four hour day. For example, .875 amounts to 875/1000 of 24 hours, which is the 21st hour or 9:00 PM.

Calculating Working Days

You can calculate the total number of days between two dates easily, because Access can perform the date arithmetic for you. For example, the date/time expression #12/24/2008# - #12/20/2008# would return 4, which is the interval between the two dates. To specify a literal date/time, enclose the date and time within pound signs (#). When you do not provide the time portion, it uses 00:00. Access performs the operation by first converting each date to its internal serial number and then performing the calculation. In this example, the calculation becomes 39806 – 39802 = 4. Because the number to the left of the decimal point in a serial number represents the number of days elapsed since the base date, the expression returns a number expressed in days, in this case 4.

However, the total number of days is not the result that you want because it includes weekends and holidays. To get the number of working days, you must first determine the number of weekdays and holidays that occur between the two dates. To do this, you can use VBA to create a user-defined function.

Access includes built-in functions that you can use in your database applications to perform calculations, or work with date/time values. A function is a procedure that performs a unit of work and often returns a result. For example, the DateDiff function computes the number of intervals between two date/time values. An interval can be a day, week, month, year, or any number that represents the type of time interval that you want to use to calculate the difference between the two date values. You can supplement the built-in library of functions, such as DateDiff, with your own user-defined functions. For example, in the following VBA code example, the Workdays function is used. You pass it a start date, an end date, and the name of a table or query that contains holidays, and it returns the number of working days.

Public Function Workdays(ByRef startDate As Date, _
     ByRef endDate As Date, _
     Optional ByRef strHolidays As String = "Holidays" _
     ) As Integer
    ' Returns the number of workdays between startDate
    ' and endDate inclusive.  Workdays excludes weekends and
    ' holidays. Optionally, pass this function the name of a table
    ' or query as the third argument. If you don't the default
    ' is "Holidays".
    On Error GoTo Workdays_Error
    Dim nWeekdays As Integer
    Dim nHolidays As Integer
    Dim strWhere As String
    
    ' DateValue returns the date part only.
    startDate = DateValue(startDate)
    endDate = DateValue(endDate)
    
    nWeekdays = Weekdays(startDate, endDate)
    If nWeekdays = -1 Then
        Workdays = -1
        GoTo Workdays_Exit
    End If
    
    strWhere = "[Holiday] >= #" & startDate _
        & "# AND [Holiday] <= #" & endDate & "#"
    
    ' Count the number of holidays.
    nHolidays = DCount(Expr:="[Holiday]", _
        Domain:=strHolidays, _
        Criteria:=strWhere)
    
    Workdays = nWeekdays - nHolidays
    
Workdays_Exit:
    Exit Function
    
Workdays_Error:
    Workdays = -1
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
        vbCritical, "Workdays"
    Resume Workdays_Exit
    
End Function 

The third argument is optional. If you do not provide it, the Workdays function will use the default "Holidays" table or query, which you must provide. Either way, the table or query that contains holidays must include a Holiday column that stores the date on which each holiday occurs. Include one row for each holiday that occurs on a weekday. Figure 2 shows a sample Holidays table.

Figure 2. Sample Holidays table

The Workdays function counts the number of weekdays from the start date through the end date inclusive, and then the number of holidays. (Inclusive means that the start date is counted if it is a weekday, and the end date is counted if it is a weekday.) Then it subtracts the holidays from the weekdays to calculate the number of working days. Notice that the function includes two lines near the beginning that resemble the following.

startDate = DateValue(startDate)
endDate = DateValue(endDate)

The purpose of these two lines is to remove any time component from the startDate parameter and the endDate parameter. The DateValue function removes the time component of the date/time value, leaving only the date component. Thus, the Workdays function works with dates only and can be used to calculate the number of full working days between dates.

Note
In addition to the DateValue function to remove the time and leave only the date, there is also a TimeValue function that removes the date and leaves only the time.

The Workdays function calls a second user-defined function to count the number of weekdays. The Weekdays function shown in the following VBA code example accepts a start date and an end date and returns the number of weekdays inclusive. Inclusive means that the start date is counted if it is a weekday and the end date is counted if it is a weekday.

Public Function Weekdays(ByRef startDate As Date, _
    ByRef endDate As Date _
    ) As Integer
    ' Returns the number of weekdays in the period from startDate
    ' to endDate inclusive. Returns -1 if an error occurs.
    ' If your weekend days do not include Saturday and Sunday and
    ' do not total two per week in number, this function will
    ' require modification.
    On Error GoTo Weekdays_Error
    
    ' The number of weekend days per week.
    Const ncNumberOfWeekendDays As Integer = 2
    
    ' The number of days inclusive.
    Dim varDays As Variant
    
    ' The number of weekend days.
    Dim varWeekendDays As Variant
    
    ' Temporary storage for datetime.
    Dim dtmX As Date
    
    ' If the end date is earlier, swap the dates.
    If endDate < startDate Then
        dtmX = startDate
        startDate = endDate
        endDate = dtmX
    End If
    
    ' Calculate the number of days inclusive (+ 1 is to add back startDate).
    varDays = DateDiff(Interval:="d", _
        date1:=startDate, _
        date2:=endDate) + 1
    
    ' Calculate the number of weekend days.
    varWeekendDays = (DateDiff(Interval:="ww", _
        date1:=startDate, _
        date2:=endDate) _
        * ncNumberOfWeekendDays) _
        + IIf(DatePart(Interval:="w", _
        Date:=startDate) = vbSunday, 1, 0) _
        + IIf(DatePart(Interval:="w", _
        Date:=endDate) = vbSaturday, 1, 0)
    
    ' Calculate the number of weekdays.
    Weekdays = (varDays - varWeekendDays)
    
Weekdays_Exit:
    Exit Function
    
Weekdays_Error:
    Weekdays = -1
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
        vbCritical, "Weekdays"
    Resume Weekdays_Exit
End Function 

The Weekdays function first calculates the total number of days between the start date and the end date, inclusive. It then subtracts the weekend days to isolate the number of weekdays. To perform these calculations, you can use the DateDiff and DatePart built-in functions.

About the DateDiff and DatePart Built-in Functions

You use the DateDiff function to count the number of intervals between two date/time values. The type of interval is specified by the first argument that you pass to DateDiff. In the Weekdays function, the first use of DateDiff calculates the total number of days so that "d" is passed because that is the appropriate interval argument for days. The following table lists the appropriate interval arguments to use.

Table 1. Interval arguments

Because DateDiff counts the end date, but does not count the start date, the Weekdays function adds 1 to the total number of days to count the startDate parameter.

When you use DateDiff, think of it as counting the number of times an interval boundary is crossed. For example, when "d" is the interval argument, the type of boundary is day so that as soon as a day changes (for example, from Monday to Tuesday), that counts as a boundary crossing. If instead you use "q" for quarter, the boundary crossing occurs when the quarter changes, as would occur when December 31st becomes January 1st.

The Weekdays function also uses DateDiff to help compute the number of weekend days. The "ww" interval argument is passed to DateDiff to count the number of weeks between the start date and the end date. The number of weeks is then multiplied by the number of weekend days per week to obtain an unadjusted number of weekend days. Notice that DateDiff counts the number of weeks by counting every time that the first day of the week boundary is crossed, which is Sunday, in this case. However, even though DateDiff counts the end date if it falls on a Sunday, it does not count the start date, even when it falls on a Sunday. For that reason, you must detect when the start date is a Sunday and add 1 to adjust the number of weekend days. The weekend days calculation will also be off by one when the end day is a Saturday, so adjust for that, as well. To verify that a day is a Sunday or a Saturday, you can use the DatePart built-in function.

The Weekdays function makes two calls to DatePart. You use DatePart to extract an interval, such as the day, from a date/time value. For example, you can extract the day interval to verify that the date is a Saturday, or the quarter interval to determine what calendar quarter a date falls in. For a date/time value that includes a time component, you could extract the hour, minute, or second. The previous example uses DatePart to determine whether the start date occurs on a Sunday and whether the end date occurs on a Saturday, so the number of weekend days can be adjusted accordingly.

Creating User-Defined Functions

For a sample database that includes the user-defined functions presented here, see the Working Days Sample Database for Access 2007. This database contains the Business Data Calculations code module that contains the Weekdays and Workdays functions. To create the user-defined functions, start Access 2007 and use the following procedure.

To create the user-defined functions

1. Click the Microsoft Office Button, click Open, and then open the database where you want to insert the functions.

2. On the Microsoft Office Fluent Ribbon, click Database Tools.

3. On the Macro tab, click Visual Basic.

4. On the Insert menu, click Module.

5. At the top of the module, type the following two lines, if they are not already there.
   Option Compare Database
   Option Explicit

6. Type the Workdays and Weekdays functions exactly as you see them in the previous code example. Or, if you downloaded the sample database, copy and paste the functions from the Business Date Calculations module.

7. On the File menu, click Save.

8. Type a module name, such as Working Days, and then press ENTER.

9. On the Debug menu, click Compile.

10. On the File menu, click Close and Return to Microsoft Office Access.

After you place the functions in your database, you can use them in Access objects such as queries, forms, and reports.

Using the Workdays Function in a Query

Consider the following scenario. You plan a project and you want to display the number of working days for each of your contractors. You construct a query that includes the Project, Planning, and Contractor tables. The Planning table includes startDate, endDate, and lookup fields for the Contractor and Project tables, so that you can enter information about each contractor’s participation in each project.

To create the working days calculation, you add an expression to the Query grid. An expression is a combination of operators, control names, field names, functions that return a single value, and constant values. For example, to add the working days calculation, you place the following expression in the Field cell of a new column in the Query grid.

Working days: Workdays([startDate],[endDate])

This expression instructs Access that you want the title of the column that results to be "Working days”. You always follow the title with a colon (:). The expression also indicates that you want this column to contain the result of evaluating the Workdays function, with startDate and endDate passed as the arguments. When Access processes the query, it evaluates the expression for every row in the query result. Figure 3 shows a working days query screen shot.

Figure 3. Working days query screen shot

Using the Workdays Function in a Form or Report

You might want to display the number of working days in a form or a report. Begin by placing a text box control on the design surface of your form or report. To add the working days calculation, add an expression to the Control Source property of your text box. The Control Source property indicates to Access where it can get the data for the control. To place the working days calculation in the text box, insert the following expression in the Control Source property of the text box.

=Workdays([startDate],[endDate])

The equal sign (=) is important because it instructs Access that what follows is an expression instead of a field name.

When you create your form or report, you must ensure that the Record Source property contains the name of a table or query that includes the fields that you want to reference. In this case, the Workdays function requires the startDate and the endDate, so those must be present in the underlying table or query. Figure 4 shows a Contractor report screen shot.

Figure 4. Contractor report screen shot

Finding the Next, Previous, First, or Last Workday in the Month

You might have to programmatically find the next, previous, first, or last workday in the month. You might also need user-defined functions to perform more general date-related tasks, such as finding the next or previous weekday, or the beginning or end of a month. For more information, solutions, and an expert explanation of these concepts, see the VBA Developer's Handbook book excerpt. This text includes solutions to various workday calculations.

Conclusion

This article discusses the Workdays and Weekdays user-defined functions that you can use in a Microsoft Access database applications. The functions are authored in VBA, the programming language that is used in Access. A sample database is provided that you can download. This database introduces the DateDiff, DatePart, and DateValue functions. You can use the Workdays and Weekdays user-defined functions in your Queries, Forms, and Reports.

Download the Sample Database

You can download the Working Days Sample Database for Access 2007 that contains the module mentioned in this article.

Additional Resources

For more information about products and technologies mentioned in this article, see the following resources:

• Access 2007 Development

• Access Developer Center

• Book Excerpt: VBA Developer's Handbook

• Microsoft Office Developer Center

• Method: DCount

• Sample Database: Working Days Sample Database for Access 2007