Determining Task Cost Allocation Per Week Based On a Task's Start and End Date

[spiner55]

Hello Everyone,

I need help with 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 number of working days in that designated week to get the cost per week.

Determining Task Cost Allocation Per Week Based On a Task's Start and End Date.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2					Cost Allocation Per Week
3	Task Name	Task Cost	Task Start	Task End	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6
4	A	$ 10,000.00	11/9/2020	11/17/2020
5	B	$ 12,000.00	11/25/2020	12/2/2020
6	C	$ 5,000.00	12/3/2020	12/20/2020
7
8
9	Week #	Start	End
10	Week 1	11/9/2020	11/15/2020
11	Week 2	11/16/2020	11/22/2020
12	Week 3	11/23/2020	11/29/2020
13	Week 4	11/30/2020	12/6/2020
14	Week 5	12/7/2020	12/13/2020
15	Week 6	12/14/2020	12/20/2020
Sheet1

 

[spiner55]

Correction: "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.