Value Distribution across columns without exceeding value

[dcalderwood]

I have been searching and searching and I have found a lot of great formulas, but none of them get me quite where I want to be. I have a worksheet that has every project we are working listed, it includes:
Column C: Start Date - let's say 4/15/2023
Column D: Number of weeks to complete - let's say 12
Column E: Calculated end date - with the examples we would see 7/8/2023
Column F: Number of hours for each department - for example Project Management would have 35 in this column
Column G forward each have a date of the week start

Row 1 is the calculated weeknum value, I'm using that in my current formula, so end date really isn't necessary:
=IF(ISBLANK($D3),"",(($F4/$D3)*AND(L$1<=(WEEKNUM($C$3,1)+$D3))))

I want to be able to distribute the 35 PM hours across all of the weeks within the date range, but I don't want it to exceed the 35 hours. I have managed to get it to distribute evenly, but I need whole number hours, and if there are an off number of hours remaining I want it to show that. For instance, the above examples provides a value of 2.91666667 to each column. If I round to 3, I end up with 36 weeks. So what I would like for it to do is distribute 3 across, and then the final week would have 2 hours. I hope this makes sense and I hope you can help. Thank you so much!

Current Formula

 

[Dave Patton]

To secure the results that you state, try the suggested solution!

 

[dcalderwood]

To secure the results that you state, try the suggested solution!

Please do not yell at me. I cannot make the start the same as the dates in row 2, projects start at all times, could be a Tuesday, could be a Thursday. This will contain a forecast of all projects we have in flight and incoming, and the start dates vary. So what you told me to do is not feasible in this situation. I cannot force any of the project to start on a Monday.

 

[dcalderwood]

Thank you but that's not what I'm looking for. The start dates are determined by the project and entered based on planning for the project. The spreadsheet needs to automatically forecast the number of hours for each team, based on the start date, and total number of hours. I need it to distribute the hours within the timeline of the project. So that is working fine. My need is for it not to distribute them but not exceed the total hours. As in 3, 3, 3, 3, 3, 2, instead of 3, 3, 3, 3, 3, 3, which is more than that allowed hours in the project.

Please do not yell at me. I made the formula change, but I cannot make the start the same as the dates in row 2, projects start at all times, could be a Tuesday, could be a Thursday. This will contain a forecast of all projects we have in flight and incoming, and the start dates vary. So what you told me to do is not feasible in this situation. I cannot force any of the project to start on a Monday. I do appreciate your formula modification. I do still need to figure out how to make the distribution work out correctly when added up.

 

[Dave Patton]

You could allocate by number of work days for the Start week and End week.