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

[dmartin55]

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.

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

Task Allocation.png

[bebo021999]

Try in C4:

Please Login or Register  to view this content.

[dmartin55]

Try in C4:

Please Login or Register  to view this content.

This worked perfectly!! Thank you kindly. I added this formula to my Gantt chart to automatically calculate the planned value (pv) per week of a task. Here is the formula with labels added for anyone who wants to more easily understand:

Determining Task Cost Allocation Per Week Based On a Task's Start and End Date (4) (1).xlsx

[soledad]

Another way, in E4:

Please Login or Register  to view this content.

Then fill right and down