Create stacked column showing hours spent per week

[Jooey]

I want to create a chart that takes the amount of hours per project and spreads it over the time period it lasts. The chart should show a stacked column per week with hours for all projects.
X-axis: Week no. Y-axis: Hours.

 

[GraH]

Can't you add a column in your table with the formula = WEEKNUM(B2,21) or ISOWEEKNUM(B2) and use that as x-axis?
xx depends on how you count week numbers. The intellisense guides you here.

 

[Jooey]

I can use that to find the start week and the end week, and also divide the hours on the amount of weeks.
But I'm not sure where to go from that?

I have been able to create the chart correctly now by creating one column per week in the table (marked yellow). But I have typed in the numbers manually. Is there any way to automatically create the data in those columns?
- Titles in row 1 with correct week numbers for the time span of the projects (week x, week x+1, ...)
- Hours per week per project

 

[GraH]

Perhaps like so

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Name	Start	End	Hrs		14	15	16	17	18	19	20	21	22	23	24	25	26
2	Project1	1/apr	15/apr	100		28,57143	50	21,42857
3	Project2	15/apr	30/apr	200				53,33333	93,33333	53,33333
4	Project3	1/apr	30/apr	20		2,758621	4,827586	4,827586	4,827586	2,758621
5
Sheet1

Cell Formulas
Range	Formula
F2:R4	F2	=IF(ISOWEEKNUM($B2)=F$1,$D2/($C2-$B2)*(7-WEEKDAY($B2,2)), IF(ISOWEEKNUM($C2)=F$1,$D2/($C2-$B2)*(WEEKDAY($C2,2)), IF(AND(ISOWEEKNUM($B2)<F$1,ISOWEEKNUM($C2)>F$1), $D2/($C2-$B2)*7,"")))

 

[Jooey]

This worked, thank you!

 

[GraH]

Nice! Thx for the feedback.