Need formula

[Ron99]

Hello,

Below is the sample data

					2-Sep-24	9-Sep-24	16-Sep-24
SNO.	START DATE	END DATE	TAT	MANHRS	Week 36	Week 37	Week 38
1	3-Sep-24	4-Sep-24	1	9	???
2	3-Sep-24	8-Sep-24	3	17	???	???
3	3-Sep-24	16-Sep-24	2	10	???	???	???

From the above table. There is start date and end date. The output I require is the formula should calculate which week does the start and end date fall and divide the manhours below should be the output.

					2-Sep-24	9-Sep-24	16-Sep-24
SNO.	START DATE	END DATE	TAT	MANHRS	Week 36	Week 37	Week 38
1	3-Sep-24	4-Sep-24	1	9	9
2	3-Sep-24	8-Sep-24	3	17	8.5	8.5
3	3-Sep-24	16-Sep-24	2	10	3.33	3.33	3.33

I have a huge data the data goes further down and across. Please help.

Thanks,
Ron

 

[KOKOSEK]

Hmmm row 2 should be only for week 1, I guess.

 

[Ron99]

Yes, you are right. My bad. Let's consider the row 2 end date as 9-sep-24. Any help on the formula ?

 

[hagia_sofia]

My apologies, I have not taken into account year-to-year transition...

 

[hagia_sofia]

Second attempt:

=IF(MAX(MIN(F$1+7,$C3)-MAX(F$1,$B3)+1,0)>0,$E3/((($C3-MOD($C3-2,7)-($B3-MOD($B3-2,7)))/7)+1),"")

 

[kvsrinivasamurthy]

In F3 then copied to full range.

=IF((F$1<=$C3)*((F$1+6)>=$B3),$E3/(MATCH($C3,$F$1:$J$1,1)-MATCH($B3,$F$1:$J$1,1)+1),"")

 

[Ron99]

In F3 then copied to full range.

=IF((F$1<=$C3)*((F$1+6)>=$B3),$E3/(MATCH($C3,$F$1:$J$1,1)-MATCH($B3,$F$1:$J$1,1)+1),"")

View attachment 116761

This is perfect! thank you. This one works too.

Second attempt:

=IF(MAX(MIN(F$1+7,$C3)-MAX(F$1,$B3)+1,0)>0,$E3/((($C3-MOD($C3-2,7)-($B3-MOD($B3-2,7)))/7)+1),"")

Thank you very much. for yout time. This works.