Across multiple rows, for each weekday in-between two dates, calculate a value, then sum by week and group by team member's name

Question

I'm working on a team workload tracker to prevent my coworker from being overloaded with work and help them prioritize.

For this task, I must use Google Sheets.

Explanation:

Peter, John and Harry are all team members. They handle user requests all day every day. When they receive a request, they must enter into a table that looks like the "sample data" tab:

• the workload (in days) they think they'll need to address it;
• the date they'll start working on it;
• the date they must finish it by.

End goal:

This is my end goal. I would like to have one row per team member, and one column per week of the year that shows this team member's total weekly workload across all the requests he's handled that week.

Notes:

• They can have concurrent requests.
• Requests can span multiple workweeks.
• They work Monday-Friday.
• A request that takes, say, 2 days to complete does not have to be completed in the 2 days that follow its start date (because it might not be urgent). For instance, a 2-day request that a team member decides to do over a period of 10 working days will take them (2/10) = 0.2 workday per day every day for 10 days.
• Each member can (but shouldn't) have a weekly workload equal or greater than 5.

What I've done:

• I've messed around with Pivot Tables but couldn't get to my end goal.
• I've done the day view. I think I shouldn't use a column per day and somehow need to do all the calculation in (array?) formulas to get to the desired end goal, but I can't figure it out.

Any help would be greatly appreciated!

Answer 1

use:

=ARRAYFORMULA(QUERY(IFERROR(SPLIT(FLATTEN(IF(SEQUENCE(1; MAX(E2:E10-D2:D10))<=E2:E10-D2:D10+1; 
 A2:A10&"×"&WEEKNUM(D2:D10+SEQUENCE(1; MAX(E2:E10-D2:D10); 0); 2)&"×"&
 INDEX(SORT(QUERY(QUERY(SPLIT(FLATTEN(IF(SEQUENCE(1; MAX(E2:E10-D2:D10))<=E2:E10-D2:D10+1; 
 WEEKNUM(D2:D10+SEQUENCE(1; MAX(E2:E10-D2:D10); 0); 2)&"×"&C2:C10&TEXT(D2:D10+SEQUENCE(1; 
 MAX(E2:E10-D2:D10); 0); "×ddd×")&ROW(D2:D10); )); "×"); 
 "where not Col3 matches '^$|sam.|dim.|53'"; ); 
 "select Col2/count(Col1),Col4 group by Col2,Col4 label Col2/count(Col1)''"); 2; 1);; 1)&
 TEXT(D2:D10+SEQUENCE(1; MAX(E2:E10-D2:D10); 0); "×ddd×"); )); "×")); 
 "select Col1,sum(Col3) where not Col1 matches '53|^$' 
  and not Col4 matches 'sam.|dim.' group by Col1 pivot Col2"))