Dave's Adventures in Business Intelligence

Determine Days To Add

Knowing the number of extra days isn’t good enough, unfortunately. To take the next step I need to know which day the date range starts on. If the date range starts on Friday and runs for 15 days, I will have Friday to Thursday twice (full weeks), plus an additional Friday. Friday is not a weekend day so I want to count it. However, if I have a 15 day range that starts on a Saturday, then I will have Saturday to Friday twice plus an additional Saturday. In this case I don’t want to count the extra day because it’s on a Saturday, which is not a business day. Even a one-day range has to be carefully checked. Monday – Monday (same day) is one business day. Sunday – Sunday is zero business days. So clearly knowing which day my range starts on is important.

With seven different potential starting days in the week and extra day ranges from one to six I have 7 * 7 or 49 potential cases to handle. Yuck. In case you don’t see where the number 49 comes from… let’s assume the first day in the date range is Monday and I have to add 3 additional days. Since Monday is day 1, and 1 + 3 = 4, and 4 < 5, I am going to add all three days. Now change the scenario and assume that the first day in the range is Thursday and I am adding 3 days. I want to count Thursday (+1) and Friday (+1) but skip Saturday (+0) and Sunday (+0) so the net number of business days is only two days. In both cases I am counting four days (starting day + 3 additional days ), but the results are different. I may add from zero to six days (7 cases) to any of seven different starting dates, which is how I got 7 * 7 or 49 different potential cases to check. I have calculated all 49 possible results and am showing them in the following table.

Starting Day		Days to Add
Day Name	Day Number	0	1	2	3	4	5	6
Mon	1	1	2	3	4	5	5	5
Tue	2	1	2	3	4	4	4	5
Wed	3	1	2	3	3	3	4	5
Thu	4	1	2	2	2	3	4	5
Fri	5	1	1	1	2	3	4	5
Sat	6	0	0	1	2	3	4	5
Sun	7	0	1	2	3	4	5	5

The header shows the values I need to consider adding, therefore I get numbers zero through six. The row for each day shows how many days I should actually add in order to avoid counting Saturday and Sunday. The Monday row is therefore 1 2 3 4 5 5 5 as I will count two days for a range ending Tuesday, three days for a range ending Wednesday, four days for Thursday and five days for any range ending on Friday, Saturday, and Sunday. Why is Tuesday two days? Because I have to remember to count Monday. If I start on Sunday and need to go through Saturday I need to add +1 for each day of the week and +0 for Saturday and +0 for Sunday. I never add more than five days in any given scenario. Is it possible to represent this complex table with a single simple formula?

Simulating Arrays

Web Intelligence does not have a concept of arrays. Because of that, most of the solutions I have seen proposed for a Web Intelligence solution involve a massive “If” statement at about this time. I’m not going to do that. 🙂 For one thing, the “If” statement would have to be nested in order to first check the starting day, and then check the number of days to be added. It might look something like this:

if Start Date = 'Mon' and Days to Add <= 5 then Days to Add else 5
else if Start Date = 'Tue' and Days to Add <= 4 then Days to Add else 4
else if ...

In theory, that would work. However, I'm going to go back to a very old technique and ressurect it here. It involves combining data into a single string and then using offset calculations to retrieve only the value I want. Let me go back and look at the "Days to Add" table I posted a few paragraphs back. The "Monday" line looked like this:

Starting Day		Days to Add
Day Name	Day Number	0	1	2	3	4	5	6
Mon	1	1	2	3	4	5	5	5

If I retrieve only the "days to add" values they are 1 2 3 4 5 5 5. If I smash them together I get this 1234555. Tuesday is 1234445. Wednesday is 1233345. If I put those three days together I get this:

123455512344451233345

Each set of values is exactly seven characters long, and will never be more or less than that amount. I know that for certain. I also know that there will be seven different strings of values for Monday through Sunday. I also know that the DayNumberofWeek() function returns a value of 1 for Monday and 7 for Sunday. Can I do anything with all of this?

Offset and Index Calculation

I have already detailed how to get the number of full weeks between two days using this:

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

I know how many days are left to add using this formula:

Mod(DaysBetween([Start Date]; [End Date]); 7)[

I know which day my range starts on with this formula:

DayNumberOfWeek([Start Date])

To get the number of days to add, I propose this:

ToNumber(Substr("1234555123444512333451222345111234500123450123455"; ((DayNumberOfWeek([Start Date])-1)*7)+Mod(DaysBetween([Start Date];[End Date]);7) ; 1))

Which would then make the final calculation for Business Days Between look like this:

(Truncate(DaysBetween([Start Date]; [End Date]) / 7 ; 0) * 5) + ToNumber(Substr("1234555123444512333451222345111234500123450123455"; ((DayNumberOfWeek([Start Date])-1)*7)+Mod(DaysBetween([Start Date];[End Date]);7)+1 ; 1))

Does it work? Based on my testing, yes. 🙂 But how?

If anyone reading this ever wrote COBOL code (and is willing to admit it) then you might remember that data was often stored in a stream of characters. In order to be able to retrieve the desired value , the program would calculate an offset into the string and return a set number of characters. That's what I am doing here. Despite my best efforts (and the efforts of many folks on BOB) I have never seen a single simple formula that would properly return all of the correct values for the "extra days" portion of this challenge. Since it's easy enough to calculate all of the answers, that's what I have done. I know the answer to all 49 possible cases. The nasty-looking formula used above is using the day number and the number of days and calculating the offset into the string of possible answers to retrieve the one character value that is needed. That long string? It is made of of the 49 possible answers concatenated together. Think of it as an array, and the Substr() function is looking up the answer based on an array index.

It's not really an array, of course. But it works, and it keeps me from having to create a massive "If" statement to handle all 49 potential cases.

Conclusion

The formulas developed in this blog post operate strictly on a weekend / weekday basis. No attempt was made to incorporate holidays that occur during the week. Note, however, that this same approach can handle a business week that includes Saturday (six day week) quite easily. All I would have to do is modify the matrix (the array) that I am using to store the results of "days to add" and I am finished.

Would this be better done in the database? Certainly. Every project I have worked on that required this sort of logic had a calendar table with columns to support this calculation. But if you don't, this formula does seem to work.

What about leap years? They don't matter. 🙂 Everything done here is date math, and therefore would not be affected by months of different lengths. If we ever had a "leap week" then I would be in trouble.

Help Text Bug
As an aside, the help text for the DayNumberOfWeek() function in XIR2 is incorrect. It states:

Web Intelligence always treats Sunday as the first day of the week

Based on my testing this is incorrect. Sunday is always day 7, and Monday is always day 1. This factors into the math used in this post. If that ever changes, the formulas provided in this post will no longer work.