How can I allocate task across multiple departments and periods?

[charltondav]

I am trying to build a project schedule that will also rollup hours per department for each week throughout the project. Adding tasks with durations that exceed 1 week requires that I allocate these across the weeks and has proven challenging.

Current Structure:

Task Name	Start Date	End Date	Duration (Business Days)	Task Hrs Dept A	Task Hrs - Dept B	Task Hours Department C	etc.
Build Server	1/1/24	1/18/24	14	10	6	3

For simplicity I am okay equally allocating these hours across each week or am open to proportionate workday allocation into the appropriate week such that my output may look like:

Department > Week	Wk 1	Wk 2	Wk 3
Dept A	3.3	3.3	3.3
Dept B	2	2	2
Dept C	1	1	1

The project manager will define the tasks (up to 100+), the start date, the duration, and the total hours per impacted department. The rollup will be used input into a master tracker that rolls up all estimates across 20+ projects within the portfolio and manages resource capacity by department. For that reason the output format needs to be consistent.

I know that I can hammer this with formulas to make it work, but with up hundreds of tasks and this being a widely used template, I don't want to create a maintenance nightmare.