Compute hours spent on projects given resource allocation

{ 2 Comments }

In the dataset below column A has the Employee Name, column B and C are the assignment start and end dates, Column D is the location and columns E to J are the Month-Year columns.  So each row represents data for an employee on a particular project.  The numbers in range E2:J8 represent how much that particular employee is aligned to the particular project i.e. a value of 1 means that the employee is dedicated solely to that project, 1.4 means that the employee will be spending extra hours on that project and 0.1 indicates that the employee will be working on multiple other projects.

The objective is to create another column (column K in the second screenshot) which will show the number of hours the employee will spend on the project.  The number of hours will be computed as number of working days in a month (treat Saturday and Sunday as weekends) * time allocation to that project (the numbers in range E2:J8) * 8.5 hours per day for an Offshore project and 8 hours per day for other projects.

The raw data sheet looks like this

The expected result is

The figure in cell K3 has been computed as:

  • Number of working days between November 11, 2018 and November 30, 2018 are 15.  So 15 * 1 = 15
  • Number of working days between December 1, 2018 and December 12, 2018 are 8.  So 8 * 0.5 = 4
  • Total effective working days are 15 + 4 = 19
  • Since it is an Offshore project, the hours per day would be 8.5.  Therefore total effective hours: 19 * 8.5 = 161.5

I have solved this problem using 3 methods:

  1. Excel formulas – Refer worksheet named “Formula output”
  2. Power Query and PowerPivot – Refer worksheet named “Power Pivot output”
  3. Power Query only – Refer worksheet named “Power Query output”

You may download my solution workbook from here.

Leave a Comment

Your email address will not be published.

*

  • This is way too complex formula to use. use of transpose and row is a new thing to learn. But the helper column has to be manually set for each row is a pain.

    I copied the whole table down in cell A11:K18 and applied the below formula in each cell E12:J18.

    =IF($B12>EOMONTH(E$11,0),0,NETWORKDAYS(MAX($B12,(EOMONTH(E$11,-1)+1)),MIN(EOMONTH(E$11,0),$C12)))*E2*IF($D12=”Offshore”,8.5,8)

    The above formula will also work when somebody has put in the values for months in which the employee was not on assignment.

    • Thank you for your comment. In the actual question that the person had posted on the forum, he had mentioned that there were “200 month columns” starting from column E and he did not want calculations for every month (the way you are suggesting). He wanted a single result at the end.

      I agree that my suggested formula is a clunk one. That is exactly why I have shared 2 other “much more” efficient solutions.