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…

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

  1. Comment by Jansi

    Thanks a lot for sharing this, Dave. You saved lot many hours of many BOBbers!!!

  2. Comment by Kenny

    following your link (from bob), everything is clear and professional on your blog… Dave, I’m your fan !!!

  3. Comment by Indu

    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

  4. Comment by Sedat

    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)?

  5. Comment by Dave Rathbun

    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.

  6. Comment by no1jjv

    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.

  7. Comment by no1jjv

    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

  8. Comment by no1jjv

    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))

  9. Comment by Dave Rathbun

    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.

  10. Comment by John Murphy

    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.

  11. Comment by Mark

    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.

  12. Comment by Dave Rathbun

    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.

  13. Comment by Heath Hall

    Thanks Dave. This detailed explanation was quite useful for me.

  14. Comment by Prabhakar

    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

  15. Comment by Hi Guys and Dave

    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..

  16. Comment by Dave Rathbun

    Hi, the formula has worked for all test cases that I checked.

  17. Comment by Dee

    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?

  18. Comment by Kiran

    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

  19. Comment by Kiran

    I’m sorry I should get 6 days but its giving 10 days ! :(

  20. Comment by scott burns

    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….

  21. Comment by Daniel Buvens

    Thank you for this elegant and simple solution. I never thought of using an array-like structure. You saved me many hours of work!

  22. Comment by Adam

    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

  23. Comment by Adam

    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

  24. Comment by ZicherCZ

    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 world-wide company ;)

  25. Comment by David Lai

    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

  26. Comment by David Lai

    Also Thanks Dave for the awesome solution!
    The array idea is brilliant ;)

  27. Comment by Rogerio Goulart

    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.

  28. Comment by David

    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])

  29. Comment by Dave Rathbun

    Looks very simple. :) Thanks for sharing.

  30. Comment by Christoph

    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.

  31. Comment by Scott

    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

  32. Comment by Adil

    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.

  33. Comment by npn

    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?

  34. Comment by Dave Rathbun

    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.

  35. Comment by Maruthi

    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 24-Oct-2011. Than it has to consider the data from 1st Data Provider till 24-Oct-2011 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 25-Oct-2011 to 31-Oct-2011).. 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.

  36. Comment by bojunior

    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..

  37. Comment by Dave Rathbun

    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.

  38. Comment by Dave Rathbun

    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:

    Start         End          Days
    2/1/12       3/31/12      43
    2/2/12       3/31/12      42
    2/3/12       3/31/12      41
    2/4/12       3/31/12      40
    2/5/12       3/31/12      40
    2/6/12       3/31/12      40
    2/7/12       3/31/12      39
    …
    3/23/12      3/31/12      6
    3/24/12      3/31/12      5
    3/25/12      3/31/12      5
    3/26/12      3/31/12      5
    3/27/12      3/31/12      4
    3/28/12      3/31/12      3
    3/29/12      3/31/12      2
    3/30/12      3/31/12      1
    3/31/12      3/31/12      0	

    (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.

  39. Comment by bojunior

    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.

  40. Comment by Dave Rathbun

    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. :)

  41. Comment by Dan

    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.

  42. Comment by Shashi

    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.

  43. Comment by Dave Rathbun

    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.

  44. Comment by Dave Rathbun

    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.

  45. Comment by Fara

    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

  46. Comment by David Gilbertson
  47. Comment by Todd Berkowitz

    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.

  48. Comment by Jessica

    I’m now a Logistics department hero thanks to you! Many thanks for your support and fantastic explanation!

  49. Comment by Anu

    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

  50. Comment by Dave Rathbun

    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 string-array trick…

  51. Comment by Anu

    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

  52. Comment by Dave Rathbun

    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?

  53. Comment by Harry

    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?

  54. Comment by Bright Bert

    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.

  55. Comment by Dave Rathbun

    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.

  56. Comment by Jayavidhya

    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 sy-tabix,
    w_date TYPE sy-datum.
    READ TABLE l_t_range WITH KEY
    fieldname = ‘ZKLSDAT7′.
    l_idx = sy-tabix.
    w_date = sy-datum – 7.
    l_t_range-sign = ‘I’.
    l_t_range-option = ‘BT’.
    l_t_range-fieldname = ‘ZKLSDAT7′.
    l_t_range-low = w_date.
    l_t_range-high = sy-datum.
    MODIFY l_t_range INDEX l_idx.
    l_t_range-fieldname = ‘ZKLSDAT7′.
    l_t_range-sign = ‘I’.
    l_t_range-option = ‘BT’.
    l_t_range-fieldname = ‘ZKLSDAT7′.
    l_t_range-low = 0.
    l_t_range-high = 0.
    append l_t_range.
    p_subrc = 0.

  57. Comment by Dave Rathbun

    I’m sorry, but I have no idea what that code is. :-?

  58. Comment by Casey

    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

  59. Comment by Dave Rathbun

    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. :)

  60. Comment by shekhar

    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

  61. Comment by Dave Rathbun

    Hi, in my opinion, that sort of complex requirement would be better suited to a custom database function.

  62. Comment by Shantanu Sharma

    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.

  63. Comment by Shantanu Sharma

    Need your help in finding a scenario where in above variable might fail.

  64. Comment by Shantanu Sharma

    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

  65. Comment by ChrisBelgium

    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 !

  66. Comment by Dave Rathbun

    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. ;)

  67. Comment by ChrisBelgium

    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).

  68. Comment by Dave Rathbun

    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.)

  69. Comment by ChrisBelgium

    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 :-)

  70. Comment by Neal G.

    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?

  71. Comment by devz

    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:00-21:00) AND NIGHT_HOURS (21:00 – 06:00)

    Thanks a lot in advance

    Dev

  72. Comment by Gokila

    Hi Dave,

    Can we change the system UDF data type for an instance? – Since I have issue in using DaysBetween() function.

    Thanks,Gokila

  73. Comment by 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?

  74. Comment by Dave Rathbun

    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.

  75. Comment by Sunny

    Hi Dave,

    This is an awesome post. Thank you Dave.

    Did anyone tried excluding holidays.

    Your help is much appreciated!

  76. Comment by Dave Rathbun

    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. 8-)

  77. Comment by Mili

    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.

  78. Comment by Dave Rathbun

    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?

  79. Comment by Mili

    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 .

  80. Comment by Jenny

    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 Mon-Fri (7am-7pm). the duration here is around 2hrs and 5min.

    Thanks in advance.

  81. Comment by Mili

    Thanks Dave. I created a Business days between function and it worked like a charm :)

  82. Comment by samson

    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

  83. Comment by Dave Rathbun

    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.

  84. Comment by samson

    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 01-SEP-2014(first day of previous month) and 30-SEP-2014 (last COB date of previous month) in the report. I know the logic to display 01-SEP-2014 but i need to logic to display 30-SEP-2014 .

  85. Comment by Hugo

    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

  86. Comment by Veenu Arora

    Thanku so much Dave for sharing this :-)

  87. Comment by John

    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

  88. Comment by Empee1

    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 7-day periods), so 6 columns is sufficient!

  89. Comment by Joel

    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??

  90. Comment by Vishal

    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?

  91. Comment by Dave Rathbun

    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.

  92. Comment by Moses

    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)*(8-DayNumberOfWeek([Assigned Status Date])));RelativeDate([Completed Status Date];Floor(DayNumberOfWeek([Completed Status Date])/6)*(8-DayNumberOfWeek([Completed Status Date]))))-(Floor(DaysBetween(RelativeDate(RelativeDate([Assigned Status Date];Floor(DayNumberOfWeek([Assigned Status Date])/6)*(8-DayNumberOfWeek([Assigned Status Date])));(DayNumberOfWeek(RelativeDate([Assigned Status Date];Floor(DayNumberOfWeek([Assigned Status Date])/6)*(8-DayNumberOfWeek([Assigned Status Date]))))+1)*-1);RelativeDate([Completed Status Date];Floor(DayNumberOfWeek([Completed Status Date])/6)*(8-DayNumberOfWeek([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

  93. Comment by Jose

    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.

  94. Comment by Lee

    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.

  95. Comment by Dave Rathbun

    Great, Lee, thanks for sharing your update.

  96. Comment by Bala

    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 .

  97. Comment by Muller

    Great !
    You save my life… in fact you save mu project :)

Leave a Reply

If you want to include formulas or code in your comment, please read my Tips for formatting comments first. Tags you can use are listed below.

XHTML: You can use these tags: <a href="" title=""> <acronym title=""> <blockquote cite=""> <code> <em> <strike> <strong> <sup> <sub> <u>

Confirm submission by clicking only the marked checkbox:

     **         

Please remember that comments that are not related to this blog post may be ignored or deleted without notice. If you're looking for help on a topic you have already posted on BOB then please do not repost your question here.