Exclude weekend hours between two days

[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!

[former_member492038]

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

[former_member492038 · Accepted solution]

Hi Pavel,

I'm going to second what kohesco 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

[kohesco]

Hi,

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

Grtz

Koen