Allocate costs by Week

[_eNVy_]

Large dataset with varying Start and End Dates.
The Week Numbers need to fall in financial year (01/04/2023 - 31/03/2023).
01/04/2023 needs to be Week 1, 10/04/2023 is Week 2 and so forth with possible carry over into the new year.

Using the Start and End Date, allocate the weekly cost to the weeks relating to the Start and End Dates.
e.g Weekly Cost is £1,000 during the course of 3 weeks, easy £1,000 per week for 3 weeks.
If an End Date is during a Week, calculate the point difference.

How do I allocate the Weekly Cost and any cost that ended during a week to its appropriate Week Number?

This will span across 52 weeks.

 

[kvsrinivasamurthy]

Data 1st Row is 5
In Q5 then copied to full range.

=($P5/$M5)*(MEDIAN(MEDIAN($K5,IF(R$3>0,R$3,Q$3+7),Q$3),$A$3,$B$3)-MEDIAN(MEDIAN($J5,IF(R$3>0,R$3,Q$3+7),Q$3),$A$3,$B$3))

 

[_eNVy_]

Data 1st Row is 5
In Q5 then copied to full range.

=($P5/$M5)*(MEDIAN(MEDIAN($K5,IF(R$3>0,R$3,Q$3+7),Q$3),$A$3,$B$3)-MEDIAN(MEDIAN($J5,IF(R$3>0,R$3,Q$3+7),Q$3),$A$3,$B$3))

View attachment 102412

Beautiful.

Thank you so much!