Oct 23 2009
Calculating Business Days Between Two Days Via Report Functions
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 sevent.
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…
Pages: 1 2
Thanks a lot for sharing this, Dave. You saved lot many hours of many BOBbers!!!
following your link (from bob), everything is clear and professional on your blog… Dave, I’m your fan !!!
Hi Dave,
Thanks for sharing this.
Just a clarification… working on these lines, how could we define and build our own calender for webi,(calendar which comes as a date picker is default), which is an applet, from which we choose dates in the prompt in webi in infoview ?
This May be a loud thought. Wondering whether we could do that.
BTW, Your dynamic dates .. suggestions… have always come in helpful for me. Thanks a lot for that info.
With kind regards
indu
I HAVE 2 COLUMS, ONE FO THEM “SKU number”, THE OTHER ONE IS “Sales revenue”. THESE 2 BRING ME THESE VALUES
SALESE REVENUE = GIVING RESULTS FROM 30 DOLLARS TO 5425687, AND APPROXIMATELY I AM GETTING 260 VALUES.
WHAT I WANT TO DO IS THIS; I WANT TO MAKE 3 GROUPS (A,B,C,), FROM 0 TO 20000 SHOULD BE PUT IN GROUP A.
FROM 20000 TO 40000 SHOULD BE PUT IN GROUP B.
FROM 40000 TO 60000 SHOULD BE PUT IN GROUP C.
.
.
.
etc
BSICALLY, I WANNA MAKE A GROUP BASE ON THE 20000 SPACE. AND THE LAST SPACE IS BETWEEN 5420000 AND 5440000.
I WOULD LIKE TO WRITE A FUNCTION IN ORDER TO DO THAT. COULD YOU PLEASE HELP ME ON THIS (How can I do that)?
Hi, Sedat. This question doesn’t really relate to this post so I’m not going to answer here. If you look at the Floor() function (or search on BOB) you might get some ideas. Thanks.
Hello!
I’m not able to get this to work for the following dates:
Start: 4/2/10
End: 4/9/10
The formula calculates 10 business days rather than 5.
Start: 4/19/10
End: 4/26/10
The formula calculates 6 business days rather than 5.
It works fine for my many other dates; it’s just these 2 that are coming up with the wrong calculation. Any advice?
Thanks.
Actually, I realize now that the formula doesn’t work for any of my dates that should show 5 business days.
Another example:
Start: 9/10/2007
End: 9/17/2007
I did some additional research, and it seems like the formula may not work when business days are multiples of 5. For example, for these dates:
Start: 4/5/2010
End: 4/19/2010
The calculation should return 10 business days; rather, it returns 11 business days.
I’ve used this formula for several of my reports only to find out now that there is an issue. Any light on how this can be resolved would be much appreciated. At this time, I am thinking to exclude the latter half of the formula if the first half equals to 5 though this will need further testing.
First half: (Truncate(DaysBetween([Start Date]; [End Date]) / 7 ; 0) * 5)
Latter half: ToNumber(Substr(”1234555123444512333451222345111234500123450123455″; ((DayNumberOfWeek([Start Date])-1)*7)+Mod(DaysBetween([Start Date];[End Date]);7)+1 ; 1))
Hi, and thanks for your input. I thought I had tested the appropriate cases but will set up something and see if I can recreate the issue.
Dave thank you. You saved me many hours. I just needed to make some minor changes. [ to ( and ; to , for Business Objects It works like a charm.
I laughed when I saw this solution as, a) it is clever, and b) it is clunky (IMO). Not to say clunky is bad – if it achieves the result then its good – but I was convinced there was a simpler and more elegant way to achieve this.
I have to admit defeat however, kind of at least – I think I have a solution that works and does not use an index/lookup, but uses 4 Ifs (which a lot less than 49).
To simplify a little, I’ve created 2 variables called [Start Day] & [End Day]:
[Start Day] = DayNumberOfWeek([Start Date])
[End Day] = [Start Day] + [Days to Add], where [Days to Add] = Mod(DaysBetween([Start Date];[End Date]);7)
[Start Day] is a number between 1 & 7, [Days to Add] is a number between 0 & 6, so [End Day] is a number between 1 & 13. An [End Day] number > 7 just means an end day into the next week.
We need to adjust the number of days to add depending on whether weekend days fall with the [Start Day] to [End Day] range.
If we think of the working days over the possible range as day numbers from 1 – 10, then effectively we want to map the actual days 1 – 13 to working days 1 – 10, by ignoring or removing the weekend days. In working days, day 6 is the Monday after day 1 and so on.
If we check the day number of [Start Day] and [End Day] we can make appropriate adjustments to a working day number, as follows:
[Adjusted Start Day] = If [Start Day] >= 6 then 6 else [Start Day]
[End Day] is bit more difficult as it can be any actual day number from 1 to 13:
[Adjusted End Day] =
If [End Day] = 6, then 5, else
If [End Day] = 13 then 10 else
If [End Day] >= 7 then [End Day] -2, else
[End Day]
End If
End If
End If
The [Adjusted Days to Add] = [Adjusted End Day] – [Adjusted Start Day] + 1
(The +1 is needed because the BetweenDays() function actually returns 1 less than the number of workings days – BetweenDays() is the number to add to [Start Date] to result in [End Date] so does not include the first day).
The full formula is:
=(Truncate(DaysBetween([Start Date]; [End Date]) / 7 ; 0) * 5) +
If([End Day]=6; 5; If([End Day]=13; 10; If([End Day]>=7; [End Day]-2; [End Day])))
– If([Start Day]>=6; 6; [Start Day]) + 1
You can substitute in the formulae for [Start Day] and [End Day] if you want to express the whole thing in just in terms of [Start Date] and [End Date]. Not a particulalrly elegant solution, but the best I could come up with without using a lookup.
Hope this post is not too long.
What is actually vexing me currently is how to use a Calendar of working/non-working days. I agree that that must be a better solution, and I have a system with a Calendar of non-working days, but I cannot work out how to use it – I’m sure it’s simple! Maybe I’ll put that in another post.
Hi, no1jjv, I took a more detailed look at your bug report. It may be that we have a difference in how Business Days are defined. In one of your examples you suggest that the range from 4/5/2010 to 4/19/2010 returns 11 business days but should return 10. I disagree.
4/5/2010 was a Monday. 4/19/2010 is also a Monday. Monday – Monday is 11 days, not 10. Monday – Friday is five days. The same is true of your other question. Basically the start day is included, otherwise 4/5/2010 to 4/5/2010 would return zero days, and in my definition is should return 1 business day.
Thanks Dave. This detailed explanation was quite useful for me.
excellent Dave, nice post which will be really useful in realtime. Keep it up. and send if u have any docs related to Bo-Designer and webi reports
Thanks,
Prabhakar.M
Dave,
Your formula was nice and is it really working…..can you clarfy me…….is it working without any issues for Business days in all cases like random dates………..please clarify me……. i have to apply for my reports, please help me sir…………any one of our group members can also explain me is it working……the dave’s formula very first one in this page..
Hi, the formula has worked for all test cases that I checked.
hi dave,
thanx for sharing the above solution. I need your help in working out the process time with business hours. I am using webi and dont have access to the universe so unable to upload a table for the dates
start date – 04/03/2011 16:00
end date – 07/03/2011 09:30
office hours is from 08:00 – 18:00
office is closed weekends
from the above scenario, the process time should be 3:30 hours
what is the best formula please?
Hi Dave,
I tested your formula to calculate working days excluding sat and sun
Here is my Input :
start date : 04/02/2010
End date : 04/09/2010
I should get output 5 days but its giving me 10 days !
can you help me on this ?
Thanks,
Kiran
I’m sorry I should get 6 days but its giving 10 days !
Everyone, I been tring for days to get this solution to work. I too have a requirement to calculate the time between two date, exclude week-end, holidays, and only counting time during business hours 7:00an – 5:00pm. I’m tring to do this BOXI 3.1 WEBI. I first tried to do it in the universe (designer) and couldn’t figure out how to build formulas there. The data source is a SQL Server db with dates stored as unixi time (sec from 1970).
Any additional help would be greatly appreciated. I’m going crazy….
Thank you for this elegant and simple solution. I never thought of using an array-like structure. You saved me many hours of work!
Here’s a fairly simple function which returns the 49 diffent extra-day values. It’s in Basic (sorry) but that does have the advantge that if you paste it into an Excel module, it becomes available as a custom function which you can then use to check that the return values are as required.
D1 is the Day Number in Dave’s 49-possible-results table. D2 is the number of Days to Add.
Public Function eDays(D1, D2) As Integer
D1 = D1 - 1
D2 = (D1 + D2) Mod 7
If D1 < 5 Or D2 5 Then D1 = 5
If D2 > 4 Then D2 = 4
eDays = D2 - D1 + 1
End If
If D2 < D1 Then eDays = eDays + 5
End Function
Sorry. I messed up the code tags.
Public Function eDays(D1, D2) As IntegerD1 = D1 - 1D2 = (D1 + D2) Mod 7If D1 < 5 Or D2 < 5 ThenIf D1 > 5 Then D1 = 5If D2 > 4 Then D2 = 4eDays = D2 - D1 + 1End IfIf D2 < D1 Then eDays = eDays + 5End FunctionHello Dave,
thanks a lot – you saved me about quite some time trying to figure this one out!
Now I only have to convince the customer that trying to include bank holidays is a BAD idea, especially in a world-wide company
Hi all,
Just a simple fix for those who are getting 10 days instead of 5 days.
Let me give you a quick example
Any date ranges that do not have a remainder:
ie: jan 1/jan 8; jan 1/jan 15; jan 1/jan 21
The reason why you are getting 10 days instead of 5 days is because in the formula, we are still adding the “tonumber(substr..” section when in reality, there is no remainder.
Therefore a simple fix is to do this
1. Create a variable “X” for “ToNumber(Substr(”1234555123444512333451222345111234500123450123455″; ((DayNumberOfWeek([Start Date])-1)*7)+Mod(DaysBetween([Start Date];[End Date]);7) ; 1))”
2. And on the final formula you can do
if(Mod(DaysBetween([Start Date];[End Date]);7) = 0) then 0
else [X]
That will solve your problem of showing 10 instead of 5 work days
Hope that helps
David
Also Thanks Dave for the awesome solution!
The array idea is brilliant
Hi everyone,
I´ve found a workaround to count the holydays too…
I´ll give an example with the e-fashion universe which, I think, will get things easier.
First of all, create an extra query filter with [Store Name]. This must be a prompt. Make the filter like [Store Nam] less than Dates (the name of the prompt). For the prompts values enter the value xxx;date1;date2;date3…
Since all soters names begins with an “e”, your query will always run.
Create a report variable (lets say holydays) that holds the SubStr(UserResponse(”Dates”);5;Length(UserResponse(”Dates”)).
You now got a array of dates separated by “;”.
Do get the individual dates, what i did was create a table with five columns :[Year],[Month],Count=RunningCount([Month]), a variable called HollydaysInBusinessDays, and another to test if the [HollydaysInBusinessDays] is between InitialDate and FinalDate (let´s say Sum)
My date formatting is “dd/MM/yyyy”.
HollydaysInBusinessDays = If(ToDate((SubStr([Dates];([Count]-1)*11+1;10);”dd/MM/yyyy”)<=5;ToDate((SubStr([Dates];([Count]-1)*11+1;10);"dd/MM/yyyy")).
Now, you got in each row of the table, the dates entered as hollydays. Test the dates against the indiviual HollydaysInBusinessDays, like If([InitialDate]<=[HollydaysInBusinessDays]and[HollydaysInBusinessDays]<=FinalDate;1;0).
Finally sum up the column Sum, and you´ll have the number of HollyDays between InitialDate and FinalDate.
Sorry for the long post, and hope this will help.
I love all the information here and found a simpler format for the variable to just count the weekdays between two dates without having to use Mod or Truncate:
=(Week([Closed])-Week([Opened]))*5+DayNumberOfWeek([Closed])-DayNumberOfWeek([Opened])
Looks very simple.
Thanks for sharing.
David, be carefull with your calculation. Using Week() Instead of Mod/Truncate is fine while having dates within the same year. But this doesn’t work as soon as you use dates of different years.
Hi,
I am using SAP Business Objects WEBI XIR2 for basic report building.
I have taken some of the above suggestions and help build a days between counting calendar.
In my scenario…our client starts their fiscal year in the 32nd week of our year. Now problem I built a routine that will return The Client’s Fiscal week number based on Today’s date. (Our client has not paid for the extra calendaring others have mentioned).
Is there a way to build a Range of Weeks? An define that range…
For example my client is trying to build moving ranges of weeks that were 3-5 weeks ago, 7-9 weeks ago, 15-23 weeks ago….and have these weeks floating….so every new week the ranges would change.
Any suggestions??
Best Regards,
Scott