Forum Discussion
Solved
Workload Projection
I am trying to work out a method of calculating our workload projection based off employee types across multiple projects. The input I have is:
- Project No
- Start Date
- End Date
- Total Project Hours for Employee Type 1
- Total Project Hours for Employee Type 2
This information is input from multiple project with varying dates and hours. In the end, I was wanting to see the total number of hours per month that each employee type is projected to have. I want this to be flexible so that I can continue to add additional projects in the future as well as extend the timeframe to always be a couple years out.
Not sure if there is other information needed to help resolve, but reach out and I will respond ASAP. I've attached the format of what I have so far, but am in no way tied to this.
Thanks for any help that can be provided!
KHarrisonSJCA Attached a quick-and-dirty Excel formula based solution. No need for PQ here, I believe, though I recommend you to learn it as it will be a life saver in many other situations.
Since you say the tool should provide a 'rough idea' only, why make things more complicated than needed. The example looks 12 months ahead from a given date. If you want to expand it to the full date spectrum of all projects, I trust you can adjust the named date ranges used yourself.
To calculate workload projections for different employee types across multiple projects and display the total hours per month, you can use Excel's Power Query and PivotTable features. This approach allows you to maintain flexibility and easily update your projections with new projects. Here's a step-by-step guide:
Assuming your data is organized in columns with headings as follows:
- A: Project No
- B: Start Date
- C: End Date
- 😧 Total Project Hours for Employee Type 1
- E: Total Project Hours for Employee Type 2
Follow these steps:
- Combine Data with Power Query:
- Select your data range (including headers) and go to the "Data" tab in Excel.
- Click on "From Table/Range" to open Power Query.
- Transform Data in Power Query:
- In Power Query, you'll need to transform the data to be more suitable for your workload projection.
- First, create a custom column to generate a list of dates for each month within the project duration. You can do this by going to "Add Column" > "Custom Column" and entering a formula that generates the list of dates.
List.Dates([Start Date], Duration.From([End Date] - [Start Date]), #duration(1, 0, 0, 0))
- Next, you can use the "Unpivot" function to transform the columns for Employee Type 1 and Employee Type 2 into two separate columns for "Type" and "Total Hours."
- Finally, extract the year and month from the date column.
- Load Data Back into Excel:
- After transforming the data, load it back into Excel. It will be loaded as a new table.
- Create a PivotTable:
- With your data now in a format suitable for workload projection, create a PivotTable.
- Select the table and go to the "Insert" tab and select "PivotTable."
- In the PivotTable Fields pane, arrange the fields as follows:
- Rows: Year and Month
- Columns: Type (Employee Type 1 and Employee Type 2)
- Values: Total Hours (sum)
- Format PivotTable:
- Format the PivotTable as needed to make it more readable. You can apply date grouping to group data by month, and you can customize the formatting of the cells.
With this setup, your PivotTable will display the total hours per month for each employee type based on the input data from multiple projects. You can easily add new projects to your data source, refresh the Power Query data, and the PivotTable will automatically update with the new workload projections.FormularbeginnThe text was created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
NikolinoDE ,
Thank you so much for your reply to get me started. I didn't get too far, however. When I added the custom colum and used the List.Dates formula, I received "Error" in those cells. See the attached screenshot and maybe you can see where I am getting tripped up.(Forgive me as the Power Query Editor is a totally new tool for me!)
That's because AI has it wrong, as most of the time when it comes to more complex Excel related issues!!
Change this:
List.Dates([Start Date], Duration.From([End Date] - [Start Date]), #duration(1, 0, 0, 0))
To this:
List.Dates([Start Date], Duration.Days([End Date] - [Start Date]), #duration(1, 0, 0, 0))
and the error will go away. However, I doubt that the rest of instructions will do you any good as they are incomplete and/or inaccurate.
In order to resolve your question, can you clarify if the example data in your screenshot is anywhere near reality? I ask because the duration in months and average monthly hours seem to be random numbers and not related to start and end dates or total hours. Important to know how you would do the scheduling manually in real life.
For the first item, for example, with a start on Jan 5, 2022 and ending on Dec 31, 2022. That's between 11 and 12 months (not 30). Do you want to divide the total hours (128) equally over 11 or 12 months, or proportionally based on the number of (working) days in a month? Or perhaps based on another method.
Always good to give a complete picture from the start. That saves a lot of guess work.
