Using a Formula To Determine A Task's Cost Allocation Per Week Based On a Task's Start and End Date

[dmartin215]

Hello Everyone,

I need help generating a formula that can do the following:

Determine how many task working days (Mon-Fri) there are given a task's start and end date. Out of those days, determine how many of those days there are in a designated week's date range, if any. Then take the task's budgeted cost and divide it by the total number of working days and multiply the cost per day by the designated number of working days in the corresponding week (if any) to get the cost per week.

Example:

Given Task As start Date range of 11/9/20-11/17/20, there are a total of 7 working days.
The cost of task A is $10,000. $10,000/7 days = $1,428.57 per day

In Week 1's range of 11/9/20-11/15/20, task A has 5 working days. Week's 1 allocation would equal $1,428.57 * 5 = $7,142.85
In week 2's range of 11/16/20-11/22/20, task A has 2 working days. Week's 2 allocation would equal $1,428.57 * 2 = $2,857.14
In week 3's range of 11/23/20-11/29/20, task A has 0 working days. Week 3's allocation would equal $0.
...etc.

[#2]

Hi @dmartin215 

Please find below construction of the formula

=ROUND($B4/NETWORKDAYS.INTL($C4,$D4,1),2)*SUM(--(IF(WEEKDAY((C4+ROW($A$1:INDIRECT("A"&($D4-$C4)+1,TRUE))-1),2)<=5,INDEX($A$10:$A$15,MATCH(($C4+ROW($A$1:INDIRECT("A"&($D4-$C4)+1,TRUE))-1),$B$10:$B$15,1)),0)=E$3))

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official/Best Answer & like it to help the other members find it more.

[#3]

@Faraz Shaikh Nice formula!! Thank you sir. Per the picture it appears the first value ($7,142) is correct, however when I open the file it reverts to $9,999. And for task B, the value for both week 3 and 4 should be $6,000.