$Account.OrganizationName
The Access Wizard Newsletter Tips Tricks and Traps for Access Users and Developers
May 2007

Date Problems - How Many Working Days Between Two Dates: Part 1

Reader (and former colleague) Mike Blumental recently wrote:

Hey Jim, do you know if there is a way to count the number of business days between two dates, so it subtracts the number of days but knows to not count weekends and holidays?

This is causing an issue in a report we have in Fla. I have some suggestions for hard coding it, but don't know if there are already tables out there on the web that developers might share.

This is a very interesting puzzle and one which is fairly complex. The solution requires not only counting the number of days between two dates, but also recognizing and eliminating the weekends (Saturdays and Sundays) and holidays.

So thanks for submitting the question, Mike, and for inspiring the topic for this month's Wizard - calculating the number of workdays between two given dates.

In this Issue
  • Tip of the Month - Make Yes/No fields Appear as Checkboxes
  • The Grand Plan
  • A Holiday Table
  • Things to Come
  • Reader Feedback

  • The Grand Plan

    Solving this question is a bit more complicated than it appears. Unfortunately, it's not as straightforward as counting the number of days and then subtracting Saturdays and Sundays; holidays must also be considered. You also can't just subtract the number of holidays between the dates after subtracting Saturdays and Sundays, because some of the holidays may also fall on a weekend.

    There are actually four steps to solving this problem:

    1. Define "what is a holiday"? Keep in mind that days off for holidays vary from organization to organization.
    2. Determine how to calculate weekdays between two dates
    3. Determine how to calculate holidays between two dates
    4. Do the math of subtracting total weekdays minus holidays

    We'll break our problem down into these bite- sized chunks. In any complex task, the best approach is to break it into smaller pieces. Although it may initially be faster to solve the whole problem in one large shot, converting it into small steps gives three main benefits:

    1. It's easier to solve a small puzzle than a large puzzle
    2. It's easier to debug a simple function than a complex function
    3. You can take a discrete function (something that does only one thing) and put in into a library of functions so that when you need it again, you don't have to reinvent the wheel.


    A Holiday Table

    What constitutes a holiday?

    What would seem at first glance to be simple is not. If you live in the United States you celebrate Thanksgiving on the 4th Thursday of November. If you live in Canada, you celebrate on the second Monday of October. If you go to a Boston Public School you celebrate March 17th ... not as you would guess for St. Patrick's Day (Erin Go Braugh!) but for Evacuation Day (the day the British ended their occupation of Boston) - sounds like an excuse to take the day off and drink Green Beer to me!

    As you can see, every company or organization will likely have their own calendar of holidays and the only realistic way to determine this is to have a table to reference as part of the calculation process.

    Creating this table is not an easy matter since it must contain all the holidays you care about. When I need to generate one of these tables for my customers I use a Web Service. That's a method of generating something from the web - really cool technology, but beyond our scope for this discussion.

    In any case for this exercise we'll use the table below.

    A holiday table is typically generated for a calendar year. For example, this table is only for 2007. It's important that your table spans the entire period you care about.

    We have four fields in this table:

    1. Holiday key - our primary key (see the June 04 issue of the Wizard for more info on the importance of primary keys).
    2. Holiday - the names that we assign to the holiday
    3. HolidayDate - Just what it says
    4. Workday - This yes/no field we use to indicate that even though it is a holiday, it's a day that we will count as a work day. You might be tempted to just delete this column as well as the rows that are workdays, however I would encourage you to use the checkbox instead since the rules may change over time and this table theoretically could handle many years. Once set up, this table can be easily updated to include/exclude holidays with an update query.


    Things to Come

    At this point we've developed a strategy for solving our problem and taken the first step of defining the holiday schedule through the creation of a holiday table. In coming issues, I'll show the logic and functions we'll use to determine the number of weekdays between two dates and the number of non-holidays between two dates.

    Once we have those pieces of the puzzle in place, it becomes a simple matter of arithmetic.


    Reader Feedback

    Sharp-eyed reader James Ling responded to last month's issue with the following:

    Your "Example 2 - Three Months Before Turning 65" seems to violate common sense unless I am missing something.

    From Access' Debug window: ? #4/26/2072# - #1/17/2072#
    100

    What three month period has 100 days?

    As you know there are intrinsic difficulties with the definition of "month" mainly due to the differing number of days our months have. For example, what is the date three months prior to May 31st? Depends on whether we are talking about some "standard" month (approximately 365.25/12 (30.4375) days) or the actual numbers of days in the months in question, etc. The HR department (or whomever) has to spell these policies out in detail to clear up these ambiguities.

    Anyway, I'm having trouble understanding your example. Thanks for you newsletter, I do enjoy reading it!

    I appreciate the kind words and, I'm glad you pointed out my mistake. In the example I had referenced April 26, 1951. When I was testing my code I used a different date; hence the error.

    James rightly points out that the definition of a month varies from organization to organization and when answering the question it's necessary to nail down exactly the question being asked. This differing interpretation also applies to holidays as we've seen in this month's Wizard. Issues like these are part of what makes date math so complex.

    In any case, James, I appreciate the feedback.


    Tip of the Month - Make Yes/No fields Appear as Checkboxes

    When you create a Yes/No field in a table you usually get checkboxes for you or your user to click to indicate Yes or No.

    If you create the field with a make-table query, or with code, it will show up as a 0/-1 rather than a checkbox. In other circumstances it may show up as, Yes/No or True/False.

    I find it's much more intuitive to see a checkbox rather than a number. If you find yourself in this position take the following steps:

    1. Open your table in design view.
    2. Click on the field that holds your True/False
    3. At the bottom of the screen you'll see a tab that says general, and another tab that says Lookup. Click the Lookup tab and change the Display Control to Checkbox.
    4. Save your table and you'll find the cryptic numbers have changed to the easy to understand checkbox.

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services



    Join our mailing list!