Skip to Content
avatar image
Former Member

Exclude weekend hours between two days

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!

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    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

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 28, 2017 at 07:32 AM

    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

    Add comment
    10|10000 characters needed characters exceeded