Working All Day – Weekends

This month, Doug Steele looks at calculating working days.

DateDiff computes how many days there are between two dates, but I want to only consider working days. Is there an easy way to ignore Saturdays and Sundays?

There are a number of ways to calculate this, some more efficient than others. For instance, you can easily write a function that loops through each day in the range, only counting those that aren’t Saturday or Sunday.

The problem with code like this, though, is that it’s inefficient. It really isn’t necessary to look at every single date in the range. It’s easy to determine how many calendar days there are between two dates using the DateDiff function. You can also use the DateDiff function to determine how many weeks there are between the two dates. Since you want to eliminate two days per week, it should be simple arithmetic, shouldn’t it? You’d expect to use code like this:

Unfortunately, it isn’t that simple. There will be problems with that calculation if the start or end date is on a weekend.

Before I give the proper equation, I think it’s important to understand how DateDiff works to calculate weeks between two dates. There are actually two different arguments you can pass to DateDiff that will calculate weeks (“w”, for weekday and “ww”, for week). They work differently, but they both return the number of weeks between two dates–sort of.

If you use “w” as the interval parameter, DateDiff looks at what weekday DateFrom is, and counts the number of times that day occurs between DateFrom and DateTo. It includes DateFrom in the count, but not DateTo.

If you use “ww” as the interval parameter, DateDiff counts calendar weeks by counting how many times the first day of the week occurs between the two dates. Note that there’s an optional fourth parameter that can be provided to the DateDiff call to specify the first day of the week. If you don’t specify, VBA assumes Sunday is the first day. If you specify the same first day of the week to the DateDiff function as the DateFrom’s day of the week, the two calculations will be identical. In other words, DateDiff(“w”, DateFrom, DateTo) will always be the same as DateDiff(“ww”, DateFrom, DateTo, Weekday(DateFrom)).

I think a visual aid may be in order. Take a look at Figure 1, which shows a calendar indicating what the days of the week are for the date range of 09 Sep, 2004 to 21 Sep, 2004. Ignoring DateFrom but including DateTo, there’s one Thursday in the range; two each of Friday, Saturday, Sunday, Monday, and Tuesday; and one Wednesday.


Figure 1

Now look at Figure 2, which shows the differences calculated by DateDiff for each of the possible values for First Day of the Week. Those various numbers correspond to what DateDiff(“ww”, DateFrom, DateTo, vbxxx) returned (where xxx is replaced with the weekday name, such as vbSunday, vbMonday, and so on). Note, however, that the values calculated by both DateDiff(“d”, DateFrom, DateTo, vbxxx) and DateDiff(“w”, DateFrom, DateTo, vbxxx) don’t depend on the value of vbxxx.


Figure 2

Since I want to eliminate Saturdays and Sundays from my calculations, I’m going to use DateDiff with the “ww” interval, with vbSunday as the first day of the week. DateDiff(“ww”, DateFrom, DateTo, vbSunday) counts how many times Sunday falls between the two dates, and I can work with that. (vbSunday is the default, but I’m going to be explicit, just in case the user’s preferences override the default.)

But knowing how many weekends occur in the range isn’t enough to be able to accurately calculate the weekdays. As I mentioned before, whether or not the start or end date is on a weekend is important. With my calculation, if the end date is a Saturday the calculation will be too large by a day, unless the start date is also on a Saturday. If the end date isn’t a Saturday but the start date is, the calculation will come up short by a day. All other combinations are fine.

Armed with this information, I can create the following function that calculates the weekdays correctly:

In this code I’m using the actual values of the constants vbSaturday and vbSunday (7 and 1, respectively) in the function to save space (I’m limited as to how many characters of code I can show on a single line in this column!). What I’m really saying is DateDiff(“ww”, DateFrom, DateTo, vbSunday) and all of the Weekday comparisons are of the form Weekday(DateValue, vbSunday) = vbSaturday.

The statement with the IIf function may be a little confusing, so I’ll explain it in a bit more detail. The syntax for the IIf function is IIf(expr, truepart, falsepart), where truepart is the value or expression returned if expr is True, and falsepart is the value or expression returned if expr is False. I’m using the IIf function to arrive at an adjustment number that I’m going to subtract from my other calculations. The first check is whether DateTo is a Saturday. If it is, I check whether DateFrom is also a Saturday. If it is, no adjustment is necessary. If it isn’t, I need to subtract one from the rest of the calculation. If DateTo isn’t a Saturday, again I check whether DateFrom is a Saturday. If it is, I need to add one to the rest of the calculation. If it isn’t, no adjustment is necessary.

Some final comments: You don’t actually need to create a function; you can put that ugly expression in-line in your SQL query. If you do that, you must use the numbers, as I did here, since the Jet engine doesn’t know anything about the constants vbSunday and vbSaturday. Putting the expression in the query itself has the advantage that you can then use the query from outside of Access. For example, if you have a Visual Basic program, you can have the query defined in your MDB, but use the query from your VB program. If the query has a user-defined function in it, such as my WorkDayDiff function, you can’t do that.

If you’re going to use a function and there’s a chance that some of the dates you pass to the function might be Null, then you should make the function’s arguments Variants, not Dates. This is especially likely if you’re going to use the function in a query, and some of the date fields in the table might be Null.

Now I know how to eliminate weekends from my dates. What about holidays?

Unfortunately, holidays can’t be excluded in such a straightforward manner. First of all, there’s the fact that holidays differ from country to country (or even state to state or province to province). Then, some holidays fall on specific dates (for example, January 1), some holidays fall on specific days (such as First Monday in September), and some have weird and wonderful formulae for their calculation (for instance, Easter is the Sunday after the Paschal Full Moon, where the Paschal Full Moon may occur from March 21 through April 18, inclusive, so that Easter is somewhere between March 22 through April 25, inclusive).

I’ve always found that the easiest way to handle holidays is to create a table of holidays in your database (yes, this means you need to include some means of adding new holidays to your application). Once you have such a table, deducting the holidays from your calculation is as simple as determining how many holiday days occur within the range.

In other words, if you have a table Holidays, with primary key HolidayDate, you can figure out how many holiday days occur in your date range with this code:

To do the same thing in SQL, you’d use this query:

Incorporating this into my WorkDayDiff function would look like this:

Remember that if any dates in the Holidays table fall on a Saturday or Sunday, they’re going to get double-counted. You’d need to alter your DCount statement accordingly if that’s a concern, like this:

If you look in the accompanying database, you’ll see I’ve actually added another field to the Holidays table, Location, so that you can represent different holidays for different locations.

Okay, I now have a replacement for DateDiff to compensate for weekends and holidays. What about an equivalent to DateAdd?

That’s a little bit more difficult, but it’s not impossible. As before, I’ll start with the simpler case: only ignoring weekends. First, if the given start date falls on a weekend, then you want to start from the previous Friday. A little trick I use is to use the Weekday function, but with a FirstDayOfWeek value of Saturday. That means that Saturdays will return a value of 1, and Sundays will return a value of 2. If the Weekday is less than 3, you need to subtract the weekday value from the date (otherwise, add nothing to the date). The DateAdd function, combined with an IIf statement, lets you do this:

Then, since you’re trying to ignore two days every week, you can actually add a week to your start date for every five working days you’re trying to add. Fortunately, you can figure out the integer number of fives in a number using the \ operator:

Of course, you want to use the adjusted DateFrom from Step 1, so the code actually looks like this:

Now, assuming that the number of working days that you’re trying to add isn’t an exact multiple of five, there’s a remainder of days left to add. The “trick” is to determine whether adding those days will cross a weekend day. If, for example, you’re trying to add days to a Thursday, you know you can add one day and it won’t be a weekend, but if you need to add two days, you’ll end up with a Saturday, so you actually need to add four days to take you to Monday. Hopefully Table 1 illustrates the adjustment that’s required.

Table 1. Adjustments required for remaining days.

Weekday Weekday Days to Add Max Days Weekday(…, vbSaturday)
  1 2 3 4    
Monday 1 2 3 4 4 3
Tuesday 1 2 3 6 3 4
Wednesday 1 2 5 6 2 5
Thursday 1 4 5 6 1 6
Friday 3 4 5 6 0 7

In Table 1, the column Max Days represents the maximum numbers of days you can add to the day without hitting a weekend day. The column Weekday(…, vbSaturday) shows the value returned by the Weekday function when you use Saturday as the FirstDayOfWeek. The formula 7 – Weekday(Date, vbSaturday) gives you the same value as Max Days. That means you need something like this code:

Recognizing that the remaining days can be calculated as NumberOfDays Mod 5, the final formula is:

As before, I used 7 rather than vbSaturday in the code in the interest of space.

To be honest, I haven’t found an easy way to include holidays in the calculation. Just as you need to adjust your starting day if it falls on a Saturday or Sunday, so too do you need to adjust it if the starting day falls on a holiday. Unfortunately, since you have no way of knowing how many days you need to adjust by (you could have two or three consecutive holiday days), that means that you can’t simply use DateAdd in your adjustment: You actually need to create a loop and continue subtracting days until you’re finally on a non-Weekend, non-Holiday day, like this:

Once you’ve arrived at a proper starting date, you can go through the same calculation as before. Once you’ve arrived at a tentative date-to, you need to check whether any holidays fall between one day after the original start date (you’ve already compensated if the start date itself was a holiday) and your tentative date-to. If there are any holidays within that range, you need to adjust the date-to accordingly. Unfortunately, you could be including yet another holiday when you do that, so you need to loop until no more holidays are found.

As you can see, that’s a rather difficult algorithm to explain, and it’s messy to implement. As a result, I’ve opted to use a variation of the crude approach I showed originally. Yes, it means you have to loop through all of the days, so the time it takes increases as the value for NumberOfDays increases, but it has the advantage of being understandable! Here’s the code:

 

 Your download file is called  Steele_SkipWeekends.accdb 
Posted in VBA | Leave a comment

When DateAdd Doesn’t

Chris Weber shows you how DateAdd can give you results that you may not be expecting–and then provides the routine that will give you what you want. Along the way he introduces you to the kind of problems that crop up when you do math with the Double datatype.

We all develop applications with a blind faith in the accuracy of Access and VBA’s intrinsic functions. Microsoft has evolved these libraries of procedures and methods for our general use and continues to grow them to meet our development needs. Our faith in this out-of-the-box code is usually well founded, but occasionally you’ll bump into an anomaly, and a crash normally follows a bump. If you use the DateAdd function–or any function that returns a Double datatype, however cleverly hidden–you get unexpected results. I’m going to point out a problem with that very useful function and provide a workaround that solves the problem. You might want to check to make sure that you don’t have a program that’s generating errors due to this “feature.” Continue reading

Posted in VBA | Leave a comment

Using FTP from Access Applications

We live in a “connected” world–almost every system has to exchange data with other systems. One of the oldest and most widespread communication protocols is FTP. In this article, Bogdan Zamfir shows how you can incorporate FTP access into your applications.

If you have an application that’s in use at more than one site, you know how difficult it can be to get data or version updates to the various sites. The answer for you may be three letters: FTP. FTP stands for File Transfer Protocol. Due to its simple and reliable design, users on very different computers and operating systems can use FTP to exchange files safely and efficiently. FTP allows a client application to connect to a server to upload or download files. The client application can be any software application that knows how to handle FTP–Access, for instance. For more information, see the sidebar, “FTP in Brief.” Continue reading

Posted in VBA | Leave a comment

Using Dynamic External Event Procedures

Reusable code libraries let you deploy bullet-proof applications. Shamil shows you how to create a library of objects (with inheritance) that you can use to process Form events. All you have to do is use the WithEvents keyword and Access 97 objects.

Modern programming uses the event-driven concept of application workflow. All of the actions that your application performs are triggered by a series of events. As I’ll show, this basic concept (together with some object-oriented programming) allows you to build sound, streamlined, and reusable code. In this article, I’m going to work with the events used by Access programmers to initialize Forms and process database data. Continue reading

Posted in VBA | Leave a comment

User Preferences, Toggles, and Rocket Science

Maybe it isn’t rocket science, but maintaining user preferences will make your applications more appealing. Follow along as Danny Lesandrini demonstrates how you can maintain a variety of user-defined options.

I’m not a rocket scientist, and fortunately, I don’t need to be in order to do great things with Microsoft Access. Don’t get me wrong—there are some very complex and exotic things you can do with Access, and they’re really cool. But the pieces that you use over and over again in every application are the simple things—for instance, the code that I use to customize the user experience. Continue reading

Posted in VBA | Leave a comment