Date Problems - How Many Working Days Between Two Dates: Part 1
Reader (and former colleague) Mike Blumental
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
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
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
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.
|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
- Define "what is a holiday"? Keep in mind
that days off for holidays vary from
organization to organization.
- Determine how to calculate weekdays
between two dates
- Determine how to calculate holidays
between two dates
- Do the math of subtracting total weekdays
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:
- It's easier to solve a small puzzle than a
- It's easier to debug a simple function than
a complex function
- 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
|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
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:
- Holiday key - our primary key (see the June
04 issue of the Wizard for more info on
the importance of primary keys).
- Holiday - the names that we assign to the
- HolidayDate - Just what it says
- 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
|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.
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#
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
Thanks for you newsletter, I do enjoy reading
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
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:
- Open your table in design view.
- Click on the field that holds your
- 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.
- Save your table and you'll find the cryptic
numbers have changed to the easy to