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

Date Problems - How Many Working Days Between Two Dates: Part 2 - Eliminating Weekends

Monday - Work
Tuesday - Work
Wednesday - Work (OTHD: Over the hump day)
Thursday - Is today a holiday? No! - Work
Friday - Work (TGIF)
Saturday - Play!
Sunday - Pray and Play

Repeat

Over the last two months we've been developing code to determine the number of work days between two dates. In May we determined that we needed to have a holiday table to track non-working holidays. Last month we developed the following function to determine whether a given day is a weekend or not:

Function IsWeekend(dtm As Date) As Boolean
  Dim rv As Boolean
  Select Case Weekday(dtm)
    Case Is = vbSaturday, vbSunday
       rv = True
    Case Else
       rv = False
  End Select
  IsWeekend = rv
End Function

This month we'll write some code to count the number of weekdays between two given dates. This will bring us closer to our ultimate goal of counting the total number of workdays.

In this Issue
  • Tip of the Month - Grab and Move Multiple Controls
  • Total Weekdays - The Function
  • The Code Continues
  • Conclusion and a Look Ahead
  • Trap of the Month - Be careful when using the DateDiff Function

  • Total Weekdays - The Function

    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.


    The Code Continues

    By the time this section of code has completed we will have eliminated any beginning or ending weekend days for the period we're calculating.

    This brings us to

    lngTotalDays = dtmEnd - dtmBegin + 1

    We add one because we always count the first day.

    The next step is to calculate the total weekend days left in our period. Since we've eliminated all weekend days from the beginning and end of our time period, we can use the DateDiff function as follows:

    lngWeekEndDays = DateDiff("ww", dtmBegin, dtmEnd) * 2

    The first part of our DateDiff function uses the interval "ww" which equates to the total number of complete weeks during the defined period (it will be an integer). Behind the scenes, Access interprets the number of weeks as the number of Sundays during the period. If you remember that we have eliminated Sundays from our beginning and ending dates, the number of Sundays during our period will be equal to the number of whole weeks.

    Since any whole weeks includes two weekend days, we take the total number of complete weeks and multiple by two to get the number of weekend days.

    From here the math is straightforward:

    TotalWeekdays = lngTotalDays - lngWeekEndDays

    The total number of days during our defined period (after eliminating all weekend days at the beginning and end of our defined period) (lngTotalDays) minus the number of weekend days (lngWeekEndDays) gives us the total number of weekdays (but not workdays. . . holidays must still be considered) during our defined time period.


    Conclusion and a Look Ahead

    At this point we have almost all the pieces in place. This month we've written our function to calculate the total number of weekdays between two dates. In the May issue we set up a holiday calendar to track non-workday holidays.

    The last step will be to calculate the total number of non-workday holidays between our dates. We'll do that next month and that step will allow us to finally reach our goal of calculating the total work days for any period.


    Trap of the Month - Be careful when using the DateDiff Function
    Acess Traps

    The following comes out of Access Help.

    When interval is Weekday ("w"), DateDiff returns the number of weeks between the two dates. If date1 falls on a Monday, DateDiff counts the number of Mondays until date2. It counts date2 but not date1. If interval is Week ("ww"), however, the DateDiff function returns the number of calendar weeks between the two dates. It counts the number of Sundays between date1 and date2. DateDiff counts date2 if it falls on a Sunday; but it doesn't count date1, even if it does fall on a Sunday.

    If date1 refers to a later point in time than date2, the DateDiff function returns a negative number.

    The upshot of all this is that there are very subtle differences, predefined within Access, between the use of different intervals for calculations using DateDiff. It's always a good idea to test your logic when using this function to make sure you're getting back what you intend.


    Tip of the Month - Grab and Move Multiple Controls

    Do you ever find when working in a form or report that you have a group of labels or textboxes that you'd like to align or treat together as a group? Access has couple of methods to allow you to do this

    You may already be familiar with the first method-the lassoing technique, since it works in both Excel and Word. Hold down the left mouse key as you move your cursor around the controls. They'll be selected as your "lasso" grabs them.

    The second technique is much less obvious and very powerful. As you are working in a form or a report, there is a ruler at the top and down the side. If you click in the top ruler it will select everything beneath it; if you click on the side ruler, it will select everything to the side of it. If you hold down the left mouse key and then move your cursor everything in the region below or to the side is selected.

    From there you can align or change properties of all the controls selected.

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services



    Join our mailing list!

    Forward email

    This email was sent to jim@custom-software.biz, by jim@custom-software.biz
    Ey
     

    Custom Software | © Copyright Custom Software All Rights Reserved | Westford | MA | 01886