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.

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.

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.

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.