Nov 15 2020 04:31 PM
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.
Nov 15 2020 11:51 PM
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.
Nov 17 2020 04:30 PM
@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.