Allocate budget across weekly reporting periods

Hi everyone,

I am looking to distribute a project budget over weeks. My weeks end on a Sunday, however my tasks may start or end mid-week of these start and finish dates.

i Have the following defined cells:
Cell B4 = Start date for the Task
Cell C4 = End date for the Task
Cell D4 = Budget for the task
Cells H2 to AW2 are my Week Ending Reporting Dates

I am looking for a formula to distribute the budget to weeks based on days the task progressed for that week, i.e, if my task starts or finishes mid-week, than the value would be less than a full 7 days reporting week period.

Any help would be much appreciated.

excel.PNG

Hi All,

I believe i have resolved this issue, in some ways apart from the calculation of days in the week.

The formula i used was as follows;
=IF(AND(O2<$B4,(O2)>$B4),(WEEKDAY($B4)+WEEKDAY(O2))*$D4/($C4-$B4),IF(AND(O2<$C4,(O2)>$C4),(WEEKDAY($C4))*$D4/($C4-$B4),IF(AND($B4<O2,$C4>=(O2)),7*$D4/($C4-$B4),"")))