cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Calculating filled working hours

Hi community

I have a problem that is bugging me. I'm trying to how much logging hours our employees has entered in the system compared to the total nr of working hours for the month. If we have 20 workingdays = 160 working hours.

Sometime the employee hasnt entered any logged hours. That can be just because they havent logged anything, or that they wasnt employeed at the time.

Do you have any ideas how I should calculate this?

I have the tables like this:

Standard calander with the date marked as a workday or not. For the example i assume all months have 20 workingdays. So for every employee employed there should be 160 working hours that month.

 Calender Date Workingday ... ...

Employment date for when the employee has started working

 Employment_date EmployeeID Fromdate Todate 1 2017-11-01 2020-01-01 2 2017-12-01 2020-01-01 3 2018-01-01 2020-01-01

The table where the employees do the logging:

 Logged hours EmployeeID Month Logged hours 1 Nov 17 120 1 Dec 17 140 1 Jan 17 140 2 Dec 17 100 2 Jan 17 100 3 Jan 17 100

Expected result:

 Output EmployeeID Logged hours Total potential working hours 1 400 480 2 200 320 3 100 160
1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Calculating filled working hours

Hi,

You may refer to my solution in this file.  I have assumed working days as Monday - Friday and 8 working hours per day.  Hours will be summed up from the first date in the Calendar table till the Month/Year chosen in the slicer.

Hope this helps.

5 REPLIES 5
Super User

## Re: Calculating filled working hours

Hi,

You may refer to my solution in this file.  I have assumed working days as Monday - Friday and 8 working hours per day.  Hours will be summed up from the first date in the Calendar table till the Month/Year chosen in the slicer.

Hope this helps.

Frequent Visitor

## Re: Calculating filled working hours

Thank you for your quick reply! I have learned lots from that code

However I get an annoying error as you see below. The total add up correctly, but if I drill down on employee i get the total of the table for all employees for "hours worked" (and thus effecting hours worked until selection period). Do you know why this is happening? Potential hours are adding upp correctly per employee

Super User

## Re: Calculating filled working hours

Hi,

I do not face any such problem.  Download the revised file from here.

Frequent Visitor

## Re: Calculating filled working hours

Thank you! Works great, I probably mixed something up in my own file

Super User

You are welcome.