Skip to Content

Exclude weekend hours between two days

Sep 27, 2017 at 08:10 PM


avatar image
Former Member

Hi community,

I've got a difficult task. I need to get minutes between two date field in a report.

And this difference need to exclude weekend hours, moreover minutes need to be calculated by working hours.

Here is an example:

DATE1 20/09/2017 14:56 DATE2 27/09/2017 23:02

Need difference like DATE2-DATE1 just with working hours (9:00am till 6:00pm) 8 hours per day

How it could be resolve ?

Any help will be appreciate!

10 |10000 characters needed characters left characters exceeded

9 AM to 6 PM is 9 hours - are you assuming anything between 12 and 1 to be a lunch break?

* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
William Ayd Sep 29, 2017 at 06:12 PM

Hi Pavel,

I'm going to second what Koen Hesters said and say this is better done on the backend. Fortunately, while a backend solution is preferable, I thought this was an interesting challenge to try and tackle in the Webi layer. Below is my solution - it will take a blog post to explain so I'm not going into too much detail, but if you follow these steps it should work. The main thing here is to try and conceptually separate out weeks, days and minutes and then join them all back together to get the calculation you are looking for.

I am assuming a lunch break from 12 to 1 since you said 8 hours per day but cited hours of 9 to 6. I'm also assuming that you have two date dimensions called [start_date] and [end_date]. Adjust accordingly

First "Adjust" your dates to move them to the week start or close, assuming they fall on the weekend

Create Variable Adjusted Start

=If DayNumberOfWeek([start_date]) > 5 Then ToDate(FormatDate(RelativeDate([start_date]; 8-DayNumberOfWeek([start_date])); "MM/dd/yyyy 09:00:00") ; "MM/dd/yyyy HH:mm:ss") Else [start_date]

Create Variable Adjusted End

=If DayNumberOfWeek([end_date]) > 5 Then ToDate(FormatDate(RelativeDate([end_date]; 5-DayNumberOfWeek([end_date])); "MM/dd/yyyy 18:00:00"); "MM/dd/yyyy HH:mm:ss") Else [end_date]

Now let's calculate how many full business weeks have elapsed, along with a "remainder" of business days

Create Variable Adj Delta

=DaysBetween([Adjusted Start];[Adjusted End])

Create Variable Adjusted Weeks Between

=Floor([Adj Delta]/7)

Create Variable Adj Days Remainder

=Abs(DayNumberOfWeek([Adjusted End])-DayNumberOfWeek([Adjusted Start]))

Now we need to look at our start / end time stamps to figure out how many "business minutes" actually elapsed on the start date, or were left on the table for the end date. Also account for a lunch break from noon to 1 PM

Create Variable Adj Start Hour

=ToNumber(FormatDate([Adjusted Start]; "HH"))

Create Variable Adj End Hour

=ToNumber(FormatDate([Adjusted End]; "HH"))

Create Variable Adj Start Bus Min Elapsed

=If [Adj Start Hour] < 9 Then 480 ElseIf [Adj Start Hour] >= 18 Then 0 ElseIf [Adj Start Hour]=12 Then 300 Else 1080 - ( ( [Adj Start Hour] + ([Adj Start Hour] < 12))* 60 + ToNumber(FormatDate([Adjusted Start]; "mm")))

Create Variable Adj End Bus Min Remaining

=If [Adj End Hour] < 9 Then 480 ElseIf [Adj End Hour] >= 18 Then 0 ElseIf [Adj End Hour]=12 Then 300 Else 1080 - ( ( [Adj End Hour] + ([Adj End Hour] < 12))*60 + ToNumber(FormatDate([Adjusted End]; "mm")))

Now you have everything you need, so simply put it all together in one last variable to get your "Business Minutes"

=[Adjust Weeks Between]*5*480 + [Adj Days Remainder]*480 + [Adj Start Bus Min Elapsed] - [Adj End Bus Min Remaining]

It really is that simple

10 |10000 characters needed characters left characters exceeded
Koen Hesters Sep 28, 2017 at 07:32 AM


if your backend is BW I would recommend to do the calculation at that level if possible. So you can use the factory calendar



10 |10000 characters needed characters left characters exceeded