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 oneday 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 nastylooking 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/nonworking days. I agree that that must be a better solution, and I have a system with a Calendar of nonworking 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 BoDesigner 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 weekend, 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 arraylike structure. You saved me many hours of work!
Here’s a fairly simple function which returns the 49 diffent extraday 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 49possibleresults 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 Integer
D1 = D1  1
D2 = (D1 + D2) Mod 7
If D1 < 5 Or D2 < 5 Then
If D1 > 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
Hello 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 worldwide 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 efashion 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 35 weeks ago, 79 weeks ago, 1523 weeks ago….and have these weeks floating….so every new week the ranges would change.
Any suggestions??
Best Regards,
Scott
Hi Dave,
I saw so many comments on your research. Could you please help me out to put date minus formula? I want to minus dates and for last specific dates I want numbers in Item Count field and the value should be dynamic. for example I put =[Item Count]Where([COB Date]=Max([COB Date]))[Item Count]Where([COB Date]=Max([COB Date]). I am not getting correct result it’s just adding the number of different dates.
Hi ,
I have tried using the simple formula suggested by David : =(Week([Closed])Week([Opened]))*5+DayNumberOfWeek([Closed])DayNumberOfWeek([Opened])
unfortunately it gave unexpected results.
On analysis I found that
Week(ToDate(”01/01/2012 12:01″;”dd/MM/yyyy hh:mm”) was giving output as 52 instead of 1, but Week(ToDate(”03/01/2012 12:01″;”dd/MM/yyyy hh:mm”) was giving correct output as 1.
Can someone explain why?
npn, I believe it’s because Web Intelligence treats Sunday as day 7, meaning it’s the end of the week. So Jan 1, 2012, is the end of week 52 for the end of 2011. If you use the more complex version of the formula, I believe you’ll get the expected result.
Hi,
Thanks for the detail steps. here i am unable to compare two date values .. I have two data Providers. based on the date provided by the user it has to select the data from data Providers. For Example: if the user provides 24Oct2011. Than it has to consider the data from 1st Data Provider till 24Oct2011 and 25 Onwards it has to consider second Data Provider. ( in second data Provider it extract the data only for Prompt Date. so that values has to repeat for the 25Oct2011 to 31Oct2011).. I have used If and Days Between and tried all the options.. it will be great helpful for me.. from past 3 Days am working on the same case..
THank you.
Not working correctly? date from 1 feb 2012 till 31 march 2012. formula gives 42, however, it should be 43 ?
And how to adjust the array? my business week is from monday till saterday. (excluding sunday) Tried to modify the number array, but doesn’t work..
Maruthi, I don’t think you need DaysBetween() you simply need to check to see if a date is greater than or less than a provided date. It sounds like you’re making things more complicated than you need… or you’re not providing all of the problem definition. You can retrieve the user’s entry for the date via the UserResponse() function, and then convert it to a date using ToDate(), and then simply compare the two dates. You don’t need to think about weekends in your scenario, so it’s not really related to this post at all, at least as far as I can tell.
bojunior:
I created some sample data in a spreadsheet and used it to test the difference between Feb 1 and Mar 31 and got 43 days. This was in Web Intelligence 3.1. Perhaps you’re in a version that changes how one of the date functions works? Here’s the output I got from my test:
(Edit) I replicated the same in BI 4 and it also works. You may need to check your formula, or perhaps your data, and see where it's failing.
Indeed, apologies, it’s working, got also 43 days. i did somewhere an error but now fine. How to exclude only the sundays? I already modified the array (delete the last string of sunday), but my result is not ok.
You won’t delete anything. You have to update the array with the proper counts. The array as it stands eliminates Saturday and Sunday from the count; you need to refill the array with the proper numbers to fit your requirements. The description on how I generated the array to ignore two days is in the post. You just need to adjust the process so that you count Saturday and ignore only Sunday. I’m not going to do the work for you.
Hi Dave Great blog. Quick question. Calculating a fixed number of business days from a start date..So, I need to calculate 34 and 40 business days (respectively) from my starting date attribute. Obviously this is easy with calendar days…business days…not so much. Any guidance you can provide would be great.
Hi Dave,
We had a request to exclude only Sunday, I made few change to array it is working absolutely fine. Now the business is requesting to have the Turnover Time in actual hours/days
Date – Time Actual Time Utilized
Start Date Friday 7/27/12 3:30 PM 8:30
Saturday 7/28/12 12:00 AM 24:00:00
Sunday 7/29/12 12:00 AM 00:00
End Date Monday 7/30/12 10:30 AM 10:30
Total Time 43:00:00
If I use the formula i get 3 working days (Excluding Sunday). However the actual time utilized is 43 hours which 1.79 day i.e. 2 days approx.
I tried few things, but nothing is working out. Is there a way to get this.
Dan, that’s an interesting twist on the question! I think this is how I would approach it:
First, you know that there are 5 business days in a week. So if you are advancing 40 business days, that’s easy because 40 business days = 8 regular weeks, so 40 / 5 * 7 = 56 net calendar days. With 34 days you need to figure out how many even weeks there are. Take 34 / 5 and truncate the result, which gives you 6 total weeks. The remainder from 34 / 5 is 4, so you have to add four additional days. Here’s where the tricky part starts… if your starting day is Monday, and you add four days, they’re all business days. If your starting day is Friday, then you have to add 6 days because of the weekend. Basically you could create a new formula / matrix that works like the one in this post.
There is a post around somewhere that shows you how to get the difference in hours between two dates. It involves using DaysBetween() to get the number of full days between, and then subtracting the two times to get the rest. I suspect that if you substitute the Business Days Between logic for the DaysBetween() function you can get the answer you need.
Hi Dave (or anyone who can give solution),
I would like to display a report with a daily transaction, but to have a subtotal by weekly, and to stop the week on Saturday instead of Sunday.i am currently using LastDayOfWeek([Amount].[Production Day]) , but it shows Sunday as the last day
Please help,
Thanks,
farra
Hi Dave,
My take on the same problem… http://blog.davidg.com.au/2012/11/workdaysbetweentwodatesinwebi.html
For those of you who want to calculate a variance of Monday to Tuesday as 1 day, instead of as 2 days, below is your answer:
=(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)) – 1
For those of you who want to calculate a six day work week, which includes Saturday, below is your answer: (includes variance adjustment from above as well)
=(Truncate(DaysBetween([Start Date]; [End Date]) / 7 ; 0) * 6) + ToNumber(Substr(”1234566123455612344561233456122345611234560123456″; ((DayNumberOfWeek([Start Date]) – 1) * 7) + Mod(DaysBetween([Start Date];[End Date]);7) + 1 ; 1)) – 1
Dave, awesome post. A real life saver. Thank you.
I’m now a Logistics department hero thanks to you! Many thanks for your support and fantastic explanation!
Dave,
Thanks Dave, it is awesome post.
I am trying to create these formulas as objects in the universe . database is db2
if the object is substr(@Select(PICK\Consolidator Code),5,2) – then i don’t have parse errors/
but when i use
substr(”1234555123444512333451222345111234500123450123455″,5,2)then i get error in the universe.
please help.
Thanks
Anu
What is the error? Also, if you’re working in DB2, there are likely to be other ways to accomplish what you need rather than resorting to the stringarray trick…
Hi Dave,
In the universe i am getting “Parse Failed: Exception: DBD [IBM][CLI Driver] CLI0118E Invalid SQL syntax. SQL STATE=37000State: 37000 ” error.
I tried to create a derived table and use it Like this 
Select 1234555123444512333451222345111234500123450123455 as Array from SysIBM.SysDummy1
and then use it in
substr(@Select(Dates\Array),char((((DAYOFWEEK(@Select(PICKHeader\Date – Start Ship Date)))1)*7)+(Mod(@Select(PICKHeader\Date – Start Ship Date)@Select(PICKHeader\Date – Shipped Date),7))+1),2)
but still i get “Parse Failed: Exception: DBD [IBM][CLI Driver] [AS] SQL0171N the data type,length or value of argument “2″ of routine “SUBSTRING” is incorrect. SQL STATE=42815″ error.
Thanks
Anu
The second argument for the substr() function should be a number. Perhaps you need to take the long expression and make sure that it is converted (via cast() perhaps) to a numeric value?
Hello Dave,
Your post helped a lot to give me the number of business days between a date range. I used variable “DAYS” for getting number of business days.
Unfortunately I am facing issues while using the same variable “DAYS” in new tab report where I want “Sum([DAYS])”. It gives me wrong values.
Any guidance?
Hello Dave,
we will be grateful if you could at least throw some light on the actual formula for difference in hours. We keep getting an error using DaysBetween() function to calculate for difference in hours between two dates. The formula for calculating this “Actual Start Time and Actual Finish time” has been a long outstanding subject. Can you kindly add your input which has been very valuable.
As this is an old comment I am just now getting to answer, hopefully you have found a solution. With the only thing I have to go on being “wrong values” without any further indication, I suspect that it’s because you’re using the data out of context, meaning one or both of the days involved in the calculation are not present in the block.
Hi Dave, Need your help! I want to get last 10 business days data from my table. I have tried your previous old post and i got data including saturday and sunday. I want to exclude that..help me.
The code which i tried is:
DATA: l_idx LIKE sytabix,
w_date TYPE sydatum.
READ TABLE l_t_range WITH KEY
fieldname = ‘ZKLSDAT7′.
l_idx = sytabix.
w_date = sydatum – 7.
l_t_rangesign = ‘I’.
l_t_rangeoption = ‘BT’.
l_t_rangefieldname = ‘ZKLSDAT7′.
l_t_rangelow = w_date.
l_t_rangehigh = sydatum.
MODIFY l_t_range INDEX l_idx.
l_t_rangefieldname = ‘ZKLSDAT7′.
l_t_rangesign = ‘I’.
l_t_rangeoption = ‘BT’.
l_t_rangefieldname = ‘ZKLSDAT7′.
l_t_rangelow = 0.
l_t_rangehigh = 0.
append l_t_range.
p_subrc = 0.
I’m sorry, but I have no idea what that code is.
I have a bizarre roundabout way of doing this, but it checks out against
networkdays( , )
in Excel.I am using it to do calculated SLAs, so please persevere through the variable names.
My approach is to first find the number of weeks, subtract two days for each week that is found, and then look at the remainder.
Find out how many days in the remainder, and depending on what day the start date falls on, subtract an additional 2 days or leave it unchanged.
Required functions:
Truncate
DaysBetween
If / elseif / else
Interim calculations:
SLA Duration
————
=DaysBetween([Submit Date];[Required Resolution Date])
SLA Weeks
———
=Truncate([SLA Duration] / 7 ; 0)
SLA Remainder
————
=([SLA Duration] / 7  Truncate([SLA Duration] / 7 ; 0)) * 7
Result:
=If DayName([Submit Date]) = "Monday" And [SLA Remainder] <= 4 Then
[SLA Duration]  ([SLA Weeks] * 2)
ElseIf DayName([Submit Date]) = "Tuesday" And [SLA Remainder] <= 3 Then
[SLA Duration]  ([SLA Weeks] * 2)
ElseIf DayName([Submit Date]) = "Wednesday" And [SLA Remainder] <= 2 Then
[SLA Duration]  ([SLA Weeks] * 2)
ElseIf DayName([Submit Date]) = "Thursday" And [SLA Remainder] = 1 Then
[SLA Duration]  ([SLA Weeks] * 2)
ElseIf [SLA Remainder] = 0 Then
[SLA Duration]  ([SLA Weeks] * 2)
Else
[SLA Duration]  ([SLA Weeks] * 2)  2
… this seems to work for all durations that are not negative or 0.
Would be great if one of you guys could double check this!
I am using SAP BusinessObjects 12.1.0
Using a big “if” statement was one of the strategies I mentioned in my post. It’s certainly workable. I just wanted to find a shorter way to do it.
hi dave,
thanks for the formula for calculating number of business days between two days ,could you please do one more favour of providing the formula for the below requirement
Thanks in ADVANCE
start date – 04/03/2011 16:00
end date – 07/03/2011 09:30
office hours is from 09:00 – 18:00
office is closed weekends
from the above scenario, the process time should be 2:30 hours
Hi, in my opinion, that sort of complex requirement would be better suited to a custom database function.
Hi Dave,
Thanks for making life easier for BO developers.
I was working on the same problem and deduced following code to find business days between two dates:
= Floor(DaysBetween([startdate];[enddate])/7)*5+ (Mod(DaysBetween([startdate];[enddate]);7) If (Mod(DaysBetween([startdate];[enddate]);7)) > DayNumberOfWeek([enddate]) Then (If (Mod(DaysBetween([startdate];[enddate]);7))  DayNumberOfWeek([enddate]) >=2 Then 2 Else (Mod(DaysBetween([startdate];[enddate]);7))  DayNumberOfWeek([enddate]))Else If (Mod(DaysBetween([startdate];[enddate]);7))<= DayNumberOfWeek([enddate]) Then (If DayNumberOfWeek([enddate]) <=5 Then 0 Else (Mod(DaysBetween([startdate];[enddate]);7) 5)))
It has worked for all the test dates till now.
Need your help in finding a scenario where in above variable might fail.
actually I am getting “Age” from database
dateDIFF(dd, startdate, GETDATE ())
which I replaced with following in the code I previously posted
(DaysBetween([startdate];[enddate])
but I realsed that there is difference of 1 day so the more gerneralized formula would be
=Floor([age]/7)*5 + If Mod([age];7) 0 Then Mod([age];7) If Mod([age];7) > DayNumberOfWeek([enddate]) Then (If Mod([age];7)  DayNumberOfWeek([enddate]) >=2 Then 2 Else Mod([age];7)  DayNumberOfWeek([enddate]))Else If Mod([age];7)<= DayNumberOfWeek([enddate]) Then (If DayNumberOfWeek([enddate]) <=5 Then 0 Else If Mod([age];7) =1 Then 1 Else DayNumberOfWeek([enddate]) 5)
where [age] is difference in days between two dates
Hi Dave. I’ve tried your solution based on a six days week, and it works fine just as usual. But… I have, I think, an interesting challenge for you. My company send goods worldwide and they asked me to compute the elapsed days between the departure date and the delivery date, in working days meaning… excluding the holidays of the countries of destination. And, of course, in a report(for some reasons difficult to explain here). Any idea ? Thanks, and thanks again for your so amazing blog and solutions !
How many holiday – country combinations are we talking about here?
Of course the best solution would be to do this in the database… but I am sure you already know that.
Indeed… I know it MUST come in the database, but actualy it won’t in an acceptable time . We’re just talking about all countries around the world, with an average of 15 holidays per country, and yes over years (up to 2030). Even with a calendar or with an excel file as dataprovider, I don’t see how to have it in the report (BOXI 3.1).
If I had to do this, what I might try is to build a spreadsheet to substitute for my calendar table. The spreadsheet would have country calendars by day and would be embedded within the document as a personal data provider. (You would need to use Rich Client to start development on this report, and then use the technique outlined in the last page of this blog post to keep that XLS file static for future refreshes.)
Thanks a lot, Dave. I came across the same conclusion this morning . What I missed in my spreadsheet was to use a full calendar per country (I’ve tried with only the list of the holidays, and of course didn’t work). This makes the computation on the working days easier, as I will also use a flag to define the sundays. Thank you for the link as well, I wouldn’t have thought to check the boxes. Your blog is such a great source of inspiration and often help me to solve complex problems. Don’t hesitate to keep posting
Required functions:
Truncate
DaysBetween
If / elseif / else
Interim calculations:
SLA Duration
————
=DaysBetween([Submit Date];[Required Resolution Date])
SLA Weeks
———
=Truncate([SLA Duration] / 7 ; 0)
SLA Remainder
————
=([SLA Duration] / 7 – Truncate([SLA Duration] / 7 ; 0)) * 7
Result:
=If DayName([Submit Date]) = “Monday” And [SLA Remainder] <= 4 Then
[SLA Duration] – ([SLA Weeks] * 2)
ElseIf DayName([Submit Date]) = "Tuesday" And [SLA Remainder] <= 3 Then
[SLA Duration] – ([SLA Weeks] * 2)
ElseIf DayName([Submit Date]) = "Wednesday" And [SLA Remainder] <= 2 Then
[SLA Duration] – ([SLA Weeks] * 2)
ElseIf DayName([Submit Date]) = "Thursday" And [SLA Remainder] = 1 Then
[SLA Duration] – ([SLA Weeks] * 2)
ElseIf [SLA Remainder] = 0 Then
[SLA Duration] – ([SLA Weeks] * 2)
Else
[SLA Duration] – ([SLA Weeks] * 2) – 2
… this seems to work for all durations that are not negative or 0.
I used this setup from Casey and it was the closest to our result we need. We're over by one day. I'm trying to find what to tweak to remove a day in this overall calculation, but I'm not seeing the adjustment. Anyone have any additional ideas?
HI Dave,
Thanks a lot for the Workdays formula… I need your help to the same for Calculating Business Hours or Working Hours.
I have 2 time stamp columns , and I need to calculate DAY_HORUS (06:0021:00) AND NIGHT_HOURS (21:00 – 06:00)
Thanks a lot in advance
Dev
Hi Dave,
Can we change the system UDF data type for an instance? – Since I have issue in using DaysBetween() function.
Thanks,Gokila
DaysBetween() requires same data type to calculate the days and I find one of the date has the data type string. How can we over come this?
There are functions to convert from one data type to another. For example, ToDate() converts a string to a date, and FormatDate() converts a date to a string.
Hi Dave,
This is an awesome post. Thank you Dave.
Did anyone tried excluding holidays.
Your help is much appreciated!
Holidays are the exception to the pattern, and would have to be accounted for individually. The best solution for this ultimately is to have a “business day” indicator on a calendar table in your database.
Hi Dave,
I have a calendar table in the database with a business day indicator. I have another table that has two date fields (X and Y). There is no join between these two tables.
I need to calculate days between X and Y and excluding holidays.
To exclude the holidays, I created a variable () in my report, and then used
the DaysBetween() function Where the holiday_flag = 1 as the formula to calculate the number of holidays between the two dates.
However it was not able to determine the no of holidays between X and Y.
Your help is much appreciated.
What database are you using? In the past I have solved this by writing a database function, often called BUSINESS_DAYS_BETWEEN(). Catchy, right? The function is responsible for taking two input dates, checking the range of dates in the calendar table, and counting the number of rows where the holiday_flag = 0 and returning that value. If you can’t write a database function, you could still do this at the universe level but you’re going to need to be able to create a join from your date fields to the time dimension table. Can you do that?
Hi Dave,
Thanks for the response. I am using oracle db. Yes I would be able to create a join from the date field to the date field in the calendar table. However in my case, I have two dates field X and Y in one table. I tried using a join like the one below but it doesn’t seem to work.
Order.X = Cal.Date_value
Would I have to duplicate the calendar table and then create separate joins for different date values ? But it seems cumbersome and error prone so I am not sure. Not sure if I am making much sense here.
Any inputs on this would be highly useful .
Hi Dave,
Would you be able to help with the formula how to calculate a duration between two date excluding weekends (based on specific business hours) and also excluding wekends and holidays again (based on specific business hours). For example: startdate: 8/8/2014 6:54:45am and enddate: 11/8/2014 9:00:00am and working hours are MonFri (7am7pm). the duration here is around 2hrs and 5min.
Thanks in advance.
Thanks Dave. I created a Business days between function and it worked like a charm
Hi Dave
I have this requirement to create a WEBI report having 2 date prompts(START DATE,END DATE).
Client wants to have a condition on the prompt so that START DATE should be always less than END DATE and if the user enters a date for END DATE which is less than the START DATE , it should throw a ERROR message.
Is this functionality achievable in BO?
I am using BO3.1
Unfortunately we can’t do this, as the prompts do not have to be processed sequentially. What I mean by that is the user could enter the End Date before they enter a value for the Start Date. Ah, you say, but why can’t I validate the prompts at query run time? Unfortunately we don’t have tools to do that either. It’s one of the primary requests that I have for the next version of Designer or IDT.
Hi Dave,
I have a requirement to display data between previous months first day and previous months last COB date on passing magic date to start and end date prompts. I am retrieving the previous months lastr COB date using a custom oracle function and the report retrieves data perfectly for date range between the previous months first day and previous months last COB date .
I need to display these dates in the report too. I would like to know if there is any method to retrieve previous months last COB date in the report to display in the WEBI report .I can use the custom oracle function to retrieve it but i dont wanna use it in SELECT clause of query due to performance issue.
eg: when i pass magicdate for startdate prompt–>99/99/9999,enddate prompt–>99/99/9999,then
i need to display 01SEP2014(first day of previous month) and 30SEP2014 (last COB date of previous month) in the report. I know the logic to display 01SEP2014 but i need to logic to display 30SEP2014 .
Here’s what I did to calculate days between excluding weekends
DaysBetween(
RelativeDate([Date_1]; DayNumberOfWeek([Date_1])+1)
;RelativeDate([Date_2]; (7 – DayNumberOfWeek([Date_2])))
) +1

2*(
DaysBetween(
RelativeDate([Date_1]; DayNumberOfWeek([Date_1])+1)
;RelativeDate([Date_2]; (7 – DayNumberOfWeek([Date_2])))
) +1
)/7
(DayNumberOfWeek([Date_1])1)
(If 5 – DayNumberOfWeek([Date_2]) < 0 Then 0 Else 5 – DayNumberOfWeek([Date_2]) )
1
Thanku so much Dave for sharing this
Hi
I have achieved your results
Now I need to extend the results
I have data with weekends cut out. I need to exclude holidays as well. I will do it in hardcode way with elseif and manually modified it year by year.
I don’t know where to start, can you give me some guides? Thanks
Your table “Starting Day/Days to Add”, contains 7 colums with “Days to Add”values.
But there will never be more than 6 days in the extra range (after the 7day periods), so 6 columns is sufficient!
When I put in the following formula:
(Truncate(DaysBetween([ToDate]; LastDayOfMonth([ToDate])) / 7 ; 0) * 5) + ToNumber(Substr(”1234566123455612344561233456122345611234560123456″; ((DayNumberOfWeek([ToDate])1)*7)+Mod(DaysBetween([ToDate];LastDayOfMonth[ToDate]);7)+1 ; 1))
I get the following error message:
Invalid character ‘1.23456612345561e+048′ as position 84. (IES 10080) (WIS 10080)
Do you have any ideas on what the issue might be??
I have [Report Month] = [1,2,3,4,5,6,7,8,9,10,11,12] & [Report Year] = [2011,2012,2013,2014,2016} as separate fields.
How do I calculate number of days in a month for a given year?
For e.g for 2016 Month Feb, Number of days = 29
Need a variable calculation formula?
There are string functions that would let you concatenate the month and the year, so you can combine Feb with 2016. Then I would add ‘01′ as the day, and then use the LastDayOfMonth() function to determine the number of days in that month.
Hi Dave,
I have a requirement to calculate the business days(excluding weekends) from a given two dates and I came across your formula and it worked like a charm, But further extending I need to show the Average days when tried it is giving me wrong results I know this might be the issue with calculation context since I am no where using the respective calculated dates in the block I am using.
could you please help me with the calculation context???
Formulae I used
****************
my dates are [Assigned Status Date] and [Completed Status Date]
Date Diff:
=DaysBetween(RelativeDate([Assigned Status Date];Floor(DayNumberOfWeek([Assigned Status Date])/6)*(8DayNumberOfWeek([Assigned Status Date])));RelativeDate([Completed Status Date];Floor(DayNumberOfWeek([Completed Status Date])/6)*(8DayNumberOfWeek([Completed Status Date]))))(Floor(DaysBetween(RelativeDate(RelativeDate([Assigned Status Date];Floor(DayNumberOfWeek([Assigned Status Date])/6)*(8DayNumberOfWeek([Assigned Status Date])));(DayNumberOfWeek(RelativeDate([Assigned Status Date];Floor(DayNumberOfWeek([Assigned Status Date])/6)*(8DayNumberOfWeek([Assigned Status Date]))))+1)*1);RelativeDate([Completed Status Date];Floor(DayNumberOfWeek([Completed Status Date])/6)*(8DayNumberOfWeek([Completed Status Date]))))/7)*2)
Sum of days:
=Sum([Date Diff])In(Ticket Number;User name)
Total Tickets:
=Count([Ticket Number];Distinct)
Average days:
=Sum of days/Total Tickets
The cross tab I am using contains user name , department name
so the information is spread out like each user name by each department total tickets completed and the final column will have Average days.
please help me out with the calculation context formula or any work around for this scenario
Hello,
I didn’t manage to create the correct matrix – days to add 
I need to consider weekends as friday+saterday+sunday+monday?
José A.
This is a great help but I found the matrix needed adjustment because DayNumberOfWeek() for Sunday is showing as day 1 in BO 4.1 Moving the Sunday numbers to the beginning looks to have solved my problems.
Used 0123455123455512344451233345122234511123450012345 instead of 1234555123444512333451222345111234500123450123455.
Great, Lee, thanks for sharing your update.
My requirement is ,I have 5 date object,like A/C open date,A/C close date,A/C Frozen date,A/C X date,A/C Y date in a report.First 3 objects will come from
Universe and remaining 2 objects will be calculated in runtime.
I have a calender table whit flag indicating whether it is holiday or working day. I have to calculate daydiffernce excluding holidays for all the 5 date objects in report.
Like in report I have 5 columns LIKE TAT1=DAYSBETWEEN (date1,date2)
Tat2=DAYSBETWEEN (date2,date3)
Tat3=DAYSBETWEEN (date3,date4)
Tat4=DAYSBETWEEN (date4,date5)
Tat5=DAYSBETWEEN (date5,date1) ,all are excluding holidays.
Backend is sqlserver 2008,using deski reports3.1.
Can anyone help me on this how solve to achieve DAYSBETWEEN for multiple date objects .
Great !
You save my life… in fact you save mu project
@Lee and @ Dave
Its back to Monday as 1 in BI 4.2.
so need to use 1234555123444512333451222345111234500123450123455
I am not a developer at all so please excuse me but I’m trying to follow the forum but just can not identify the solution to calculate the number of work days between 2 dates. Say from Monday to Friday returning a count of 4 days, not including the Monday. Any help for noob?
Hi Dave,
I need to exclude the Weekends(Saturday & Sunday) if any from the Start and End dates.
I have tried with 2 formulas which you have provided in above but its not working for my dates.
Start Date: 12/09/2017 12:38:32 PM (MM/dd/yyyy hh:mm:ss A)
End Date: 12/11/2017 04:38:32 AM (MM/dd/yyyy hh:mm:ss A)
Formula 1:
=(Week([EndDate)Week([StartDate]))*5+DayNumberOfWeek([EndDate])DayNumberOfWeek([StartDate])
Result is: 0
Formula 2:
=(Truncate(DaysBetween([StartDate]; [EndDate]) / 7 ; 0) * 5)
+ ToNumber(Substr(”0123455123455512344451233345122234511123450012345″ ; ((DayNumberOfWeek([StartDate]) – 1) * 7)
+ Mod(DaysBetween([StartDate];[EndDate]);7)+ 1 ; 1) ) 1
Result is: 0
I am using the BOBJ version 4.2 SP3 Patch5.
Please advise me the solution.
Thanks in advance.
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
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 easteregg 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.
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.
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.
Dave,
Super Great on Business Days formula. I’ll admit I was COBOL programmer way back in the 1980’s1990’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.
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
I need to consider Friday and Saturday as weekends what will be change.
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))
Did anyone ever get an array that works for Sunday being the only nonworking day?
I’ve tried!!
thanks
James
@James take a look at comment #47 by Todd Berkowitz