Hey, i want to remove weekends from a daysbetween() function in webi. so i have order date and ship date but i want to take out weekends b/w the 2. thanks.
-Sam
showt2 (BOB member since 2009-05-11)
There are at least a couple of threads here on that topic. Try searching for “weekend”.
Joe
joepeters 
(BOB member since 2002-08-29)
Sam,
Please can you change the title to something more meaningful?
Many people will ignore the title.
this is the only way i know how to do that is by replying again, and i have seen the other topics and they each require about 7 variables to be created and some of the subtractions/additions i don’t understand. thanks.
showt2 (BOB member since 2009-05-11)
you can write a if statement for example…
create a variable at report level…
if(day(date) <> “Sunday” or day(date) <> “Saterday”;object)
this variable takes only week values…
i think it should work
Padma Reddy (BOB member since 2009-05-29)
but how would i implement that in my daysbetween() function. I need that number to do more calculations. I just want to remove the weekends from the count of days b/w 2 dates. I Know it has to do with If then but i can’t see how it would play out. Please explain further thanks.
showt2 (BOB member since 2009-05-11)
This question has come up quite frequently, so searching is really the best bet. 
By far the easiest way to do this is with a calendar table that marks weekend dates (and potentially holiday dates as well) and lets you work that way. If you can’t do that, you will have to resort to some tricky math. It helps if you draw a picture or look at a calendar while you’re evaluating the formulas.
First the simple part: there are 28 days between your two dates, so you are guaranteed to have at least 4 weeks. But how many weekends can you count? The easy answer is “four” which would mean you have 20 business days between your two dates. Suppose today is Wednesday and you go back 28 days, you will get 8 weekend dates in that range. Now suppose that today is Sunday, and you go back 28 days, how many weekends do you get? Again, you get 8. What if today is Friday? Still 8 days. Okay, so with a range of 28 days you always get two weekend dates. Now to make it more general: if you have any date range with an even multiple of 7 days, you can count on having exactly an even number of weekend dates.
Assume “X” is the number of days between two dates, what does this do?
truncate(X / 7, 0)It takes the number of days, divides by 7, and returns an integer result. If X < 7 you get zero. If X between 7 and 13 you get 1. If X is 14 you get 2. Why does this matter? Because you can multiply that number by the number of weeks and be guaranteed to get no fewer weekend days than that.
Why no fewer? Suppose you have a range of five days, Mon - Fri. Doing truncate (5 / 7, 0) = 0 and you take 0 * 2 and get 0 weekend days. That’s correct. If you have a range of 28 days, then truncate (28 / 7) * 2 = 8, which matches the answer given above.
But… what if the five days are Friday through the following Tuesday? That’s only five days, yet it includes weekend days that have to be removed. That means you have to know the start day as well as the total number of days in order to calculate properly. Still making sense? 
It’s for this reason that most formulas you see to do this are quite complex. You mentioned seeing some other formulas here on BOB. I suggest you go back and look at them while looking at a calendar and see if you can figure out how they work. If not, post a specific question and see if someone can help clear things up.
Or get a weekend flag in your calendar table.
[Edit] A bit of searching got me to this post which provides some of the same details I did but finishes them out more, I think.
Dave Rathbun (BOB member since 2002-06-06)
Hey, I really appreciate the explanation and i did understand it for the most part but here is my situation say if the order was on monday and got shipped out on friday. when you run the math its going to try to remove the weekends from those days as well where we don’t need anything removed just straight up bring the difference back. That is why i am confused about the other really complicated math problems they have in the other topics. thanks.
showt2 (BOB member since 2009-05-11)
That’s what I was getting at. If you have any multiple of 7 days you can guarantee you have 2 weekend days within that range. Anything left over needs to be handled based on the start date. In your case, you have zero multiples of 7, so no guaranteed weekends. You have five days left to deal with, and you need to figure out which day the range starts on in order to be able to handle it.
If the date range starts on Monday, five days = five working days
If the date range starts on Friday, five days = three working days
Dave Rathbun (BOB member since 2002-06-06)