Oct 23 2009

Calculating Business Days Between Two Days Via Report Functions

Categories: 2009 GBN - Dallas,Report Techniques,Variables!,Web Intelligence Dave Rathbun @ 7:57 pm

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…

114 Responses to “Calculating Business Days Between Two Days Via Report Functions”

  1. Comment by Sarah

    Dave – I just wanted to say a huge thanks for this! You saved my report and made me look good to boot!! The database guys couldn’t figure out how to do this in Hadoop and BO came out tops!
    We’ve even managed to institute different start and end business hours to show what would happen in a call centre if they changed their hours 🙂
    Thank goodness the internet lives on!!
    Sarah

  2. Comment by NL

    Hello Dave,

    This article without a doubt is a very helpful one like most of your articles which address the nuances of the topic. I just arbitrarily chose to thank you on this one and wish you continue doing so for as long as possible.
    PS:I’m sure you get this a lot but I wanted to let you know that I found the easter-egg on your site header *wink wink*.

    @Sarah, could you please let me know how you implemented the business hours logic in SAP BO ? Would really appreciate it thanks.

  3. Comment by Mahmoud

    Hi Genesis,
    could you please help me to create a formula to calculate hours between two dates excluding weekends ( Friday and Saturday ) for the middle east countries .

    Much appreciated.

  4. Comment by mahmoud

    Hello Dave,
    This is an awesome post. Thank you.
    Need your help for a formula to calculate hours between two dates excluding weekends ( Friday and Saturday)
    .
    Appreciate your support.

  5. Comment by Myron

    Dave,

    Super Great on Business Days formula. I’ll admit I was COBOL programmer way back in the 1980’s-1990’s, but have forgotten much of it. I have seen many other attempts to explain a formula but as odd as yours seems, you very succinctly describe every step of the way and is truly appreciated and understood.

  6. Comment by Bala

    Hi Dave,
    I have 4 date objects D1,D2,D3,D4 in master table and calendar table with Date and Flag (W/H) and need to get the daydifference between (D1,D2),(D1,D3).. excluding the holidays(H).I am using SQL server 2014 and BO 4.2.How to join Calender table and master table and how to use it in webi reports..Highly Appreciated..please

  7. Comment by sunil

    I need to consider Friday and Saturday as weekends what will be change.

  8. Comment by sunil

    For those who are looking for weekends to be Friday and Saturday below is the formula:
    (Truncate(DaysBetween([StartDate];[EndDate]) / 7 ; 0) * 5) + ToNumber(Substr(“1234555123444512333451222345111234500123450123455”; (([If(DayNumberOfWeek([StartDate])+1) = 8 Then 1 Else DayNumberOfWeek([[StartDate]])+1]-1)*7)+Mod(DaysBetween([StartDate];[EndDate]);7)+1; 1))

  9. Comment by James Halligan

    Did anyone ever get an array that works for Sunday being the only non-working day?
    I’ve tried!!

    thanks

    James

  10. Comment by Vito

    @James take a look at comment #47 by Todd Berkowitz

  11. Comment by arun

    Hi Dave, Sunil

    If I only need friday and one case only Sunday, what will be formula.

    THanks for thehelp
    Regards
    arun

  12. Comment by arun

    Hi Dave, Sunil,

    Only Friday off will that be possible

  13. Comment by arun

    Does any one has a Array for friday as the only holiday

  14. Comment by Josiah

    Is there a way to make Friday and Sunday as different weekends for different, say, countries?