Dave's Adventures in Business Intelligence

A frequently asked question on BOB is:

How can I calculate the number of business days between two dates?

The easiest answer is to use a calendar table with a flag for business days because that takes care of everything quite easily. You can even mark holidays along with weekend days and make the results more useful.

But suppose you don’t have a calendar table. Suppose you don’t care about holidays, you simply want to count the number of Monday – Friday days between a range of dates. Can you do that?

The answer is coming if you read the rest of the post. 🙂

Note: This technique was shown in public on Monday at the GBN conference. I had written this blog post some time ago but waited to release it until after the presentation had been delivered. The presentation has been uploaded to my blog and is available for download from the conference page.

Web Intelligence Functions Required

This formula is going to use the following functions, so I will introduce and describe each of them a bit here.

• DaysBetween()
  Input values: two date values
  Output value: an integer number (no fractions) showing the number of days between the two dates
• DayNumberOfWeek()
  Input value: date
  Output value: Number from 1 to 7 denoting the day of week (1 = Monday, 7 = Sunday)
• Truncate()
  Input value: numeric expression or value, number of desired decimal places
  Output value: numeric value truncated to specified number of decimals, for example Truncate(3.14159, 2) = 3.14
  Truncate does not round, so Truncate(5.9; 0) returns 5 which is what is needed for this solution
• Mod()
  Input value: two numbers
  Output value: the remainder of the first value divided by the second
  Example: Mod(10; 2) = 0 and Mod(11; 2) = 1 and Mod(15; 7) = 1
• Substr()
  Input value: string, starting position, length
  Output value: part of the string
  Example: Substr(‘1234’;2;1) = ‘2’
• ToNumber()
  Input value: string representation of a valid number
  Output value: number
  Example: ToNumber(‘2’) = 2

Counting Weeks

One of the first steps in solving any sort of calculation is to look for shortcuts. Clearly there will be an infinite number of input values for my calculation so I need some way to avoid too many possible choices. Because of this I am going to start by counting the number of weeks between two days.

Take a look at the calendar on the right-side of my blog and try an experiment. It turns out that no matter which day of the week you start on, if you run for seven days you are guaranteed to get exactly two weekend dates. No more, no less, exactly two. Try it out and see. Start on a Sunday and count forward seven days and what do you get? Exactly one Sunday and one Saturday = two weekend days. Start on Monday and count forward seven days and the same thing happens. Start on Tuesday… on Friday, or even Saturday… and any range of seven days always has exactly two weekend dates included.

I can extend this to any multiple of seven days. If I have 14 days I get 4 weekend days. With 21 days I have 6 weekend days. If I multiply by five instead of two I get the number of weekdays in the range, therefore I am going to start my calculation using this pattern. Here is the start of the formula:

Truncate(DaysBetween([Start Date]; [End Date]) / 7 ; 0)

Let me break that down. First the DaysBetween() function is used to determine the number of days between the starting and ending dates for my range. Next I divide the number of days by seven which will result in a decimal result. For example, 15 / 7 = 2.14. Finally I use the Truncate() function to remove the decimal, which converts 2.14 to 2. That gives me the number of full weeks between my two days. Here’s a table showing results for some sample day ranges.

Number of Days	Number of Whole Weeks
3	0
5	0
7	1
8	1
13	1
14	2
15	2
20	2
21	3
22	3

Based on the conclusion I made earlier, I can use the number of weeks and multiply by five to get the number of week days included in the range. In order to get that number I make one small modification to the formula presented above; I multiply the result by five. The result:

Truncate(DaysBetween([Start Date]; [End Date]) / 7 ; 0) * 5

All I have left to deal with are the leftover of days that don’t make up a full week. The decimal part of 2.14 is 0.14 which represents one extra day in my range. In order to know whether to count that day or not, I have to know the name (or alternatively the number) of the day my range starts on.

How Many Extra Days

There is a function called DayNumberOfWeek(). It numbers Monday as 1 and Sunday as 7 so I’m going to pretend that my calendar looks like this:

M T W T F S S
1 2 3 4 5 6 7

What I need to get next is the number of days that are not a full week. I used the Truncate() command earlier to get the number of full weeks. It removes the decimal part of the division result. What I need now is the decimal part, and the Mod() function can be used to get it. This function does a division and returns the remainder. The first argument will be the total number of days, and the second argument will be seven as there are seven days in a week. The result will be a number that ranges from zero (0) to six (6) because those are the only possible remainder values when dividing by seven.

For example, Mod(15; 7) = 1. 15 / 7 is 2 with 1 left over, therefore 1 is the return value from the Mod() function. To apply that to my current problem: 15 days is made up of two full weeks and one extra day. Twenty days would be Mod(20; 7) which results in 6 remaining days. Mod(21; 7) is zero because there are exactly three weeks and no extra days.

Can I put these two pieces of information together? I can easily get the number of the day in the week, and I know how many extra days there are. I should be able to do some math…