0

# Exclude weekend hours between two days

Sep 27, 2017 at 08:10 PM

81

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!

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

William Ayd Sep 29, 2017 at 06:12 PM
0

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

`=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]`

```=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

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

`=Floor([Adj Delta]/7)`

`=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

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

`=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

Share
Koen Hesters Sep 28, 2017 at 07:32 AM
0

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

Share