Forum Discussion
Workload Projection
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.
Thanks for the quick reply as well.
To your point of the project duration column, that was on me. I was using the formula "=(DAYS(C2,B2))/12" when I should have been using "=(DAYS(C2,B2))/30" to give the approximate duration in months. (Approximate due to using 30 days for each month.)
Given that this tool is intended to provide a rough idea of what we can expect in future months based off bookings, I know the reality of scheduling will differ. For simplicity I was going to divide the number of hours equally through the date range, which gives me that 11-12 month duration like you said. If we can make it more accurate to reflect the working days available in each month, that would be great, but not worth overcomplicating it.
Hope this helps! Feel free to ask more questions as needed.
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.