Oct 23 2009
Calculating Business Days Between Two Days Via Report Functions
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.
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