Now comes the critical part of the process. In
order to determine the total weekdays between
two dates we'll use
the following function:
Function TotalWeekdays(dtm1 As Date,
dtm2 As Date) As Integer
Dim lngWeekEndDays As
Long
Dim lngTotalDays As
Long
Dim dtmBegin As Date
Dim dtmEnd As Date
If dtm1 > dtm2 Then
dtmBegin =
dtm2
dtmEnd =
dtm1
Else
dtmBegin =
dtm1
dtmEnd =
dtm2
End If
Do Until IsWeekend
(dtmBegin) = False
dtmBegin =
dtmBegin + 1
Loop
Do Until IsWeekend(dtmEnd)
= False
dtmEnd =
dtmEnd - 1
Loop
lngTotalDays = dtmEnd -
dtmBegin + 1
' We add one because we
want to count the
first day
lngWeekEndDays = DateDiff
("ww", dtmBegin,
dtmEnd) * 2
' There are two days in each
weekend
TotalWeekdays =
lngTotalDays -
lngWeekEndDays
End Function
When thinking about this process, it's helpful
to remember that we are doing "date math"
and that dates on the calendar are assigned
values. For a review, see the January
2007 Issue where I mention that
Microsoft assigned December 30,
1899 the value 1; the date January 17, 2007 is
actually stored as 39099.
Per usual we'll analyze our code piece by
piece:
Our function's first line is as follows
Function TotalWeekdays(dtm1 As Date, dtm2
As Date) As Integer
As you can see we take two dates as inputs
(dtm1 and dtm2) and our function will return an
integer that will be the total number of
weekdays between the two passed in dates.
Note that we don't ask for a beginning or
ending date in any particular order. We want
to make our function as easy to use as
possible, so we'll just take in the two dates in
whatever order they are given to us.
Our next section of code sets up
or "dimensions" four variables. In these lines of
code, we are defining variables as a type ("As
Long" or "As Date") When we say that a
variable coming in must be a long, that means
that it's a whole number. Note that the variable
type is abbreviated at the beginning of the
variable name:
Dim lngWeekEndDays As
Long
Dim lngTotalDays As
Long
Dim dtmBegin As Date
Dim dtmEnd As Date
- lngWeekendDays represents the
number of weekend days to eliminate once
any weekend days at the endpoints are
determined and removed from our period.
- lngTotalDays is the total number of
days in our period, once again after having
eliminated any beginning or ending weekend
days
- dtmBegin and dtmEnd represent our
beginning and ending days respectively.
The next section of our code is used to
establish the beginning and ending dates
If dtm1 > dtm2 Then
dtmBegin =
dtm2
dtmEnd =
dtm1
Else
dtmBegin =
dtm1
dtmEnd =
dtm2
End If
Remember how we didn't care which order the
user gives the date to us? It's more convenient
and flexible for the user if we can take the
dates in either order, but since this "date
math" code does a calculation, we'll have to
determine which date is earlier (smaller) vs.
which date is later (larger). If you are
mathematically inclined, you could also attain
the same result using an absolute function,
however I find my approach a bit clearer. This
chunk of code basically says if the first date
passed in is greater (later) than the second
date then make the second date the beginning
date, otherwise make the first date the
beginning date.
Our next chunk of code eliminates any
beginning or ending weekend days by
assigning the first weekday at the beginning of
the period as dtmBegin and the last weekday
at the end of the period as dtmEnd. This code
uses a special function called IsWeekend that I
introduced in the June
2007 issue
and showed again earlier in this issue.
Do Until IsWeekend
(dtmBegin) = False
dtmBegin =
dtmBegin + 1
Loop
Do Until IsWeekend(dtmEnd)
= False
dtmEnd =
dtmEnd - 1
Loop
The Do Until syntax essentially says execute
the following piece of code until some
condition equals whatever I say it should (in
this case, until you find a date that is not a
weekend). It will continue to execute the lines
between
Do Until IsWeekend(dtmBegin) =
False and Loop
until it finds a date at the beginning of the time
period that is not a weekend.
The middle line
dtmBegin = dtmBegin + 1
just adds one day to our begin date so we can
move through the calendar (remember, all
dates are assigned an integer value). So the
function starts with the first date and moves
through the dates one by one to find the first
date that is not a weekend. It will then assign
this date to our dtmBegin variable. Likewise,
the function starts at the end of the time period
and moves through the calendar, subtracting 1
and looking for the first date that is not a
weekend. It will assign this date to our dtmEnd
variable.
