This might be more simple than I'm making it, but I have yet to find or create a working function for this problem. I have data for upcoming projects that could begin and end anytime over the next two years, i.e. 2025 & 2026. I know the estimated start date for the project, the expected duration in weeks, and the estimated total cost of the projects.
I would like to split the cost of these projects relative to their duration between each year for budgeting purposes. Column A represents the project number, Column B represents the start month of the project, Column C represents its duration in weeks, Column D represents the end date, Column E represents the total cost, and Column F represents the weekly cost. I would like Column G to represent the portion of the cost of the project in 2025 and Column H to represent the portion of the cost of the project in 2026.
The example shows what I was given: project, start date, duration (in weeks), and total cost. Other calculations for project 2, for example: end date =B3+(C3*7)
. Weekly cost =E3/C3
.
The next step - in my mind - would be (total weeks of project in 2025 * weekly cost) to calculate 2025 cost; 2026 cost = total cost - 2025 cost. I am unable to think of an equation/formula that will calculate or count the number of weeks in a date range that belong to a certain year.
The examples are easy enough to calculate manually, but with a larger data set and durations that are less divisible this becomes more difficult.
I unfortunately do not have a formula to show as an example as I'm not sure where to go from here, but I hope my explanation makes sense as to what I am looking for.
EDIT: Apologies everyone, I uploaded the incorrect date formats which resulted in wildly inaccurate examples of what I was looking for originally. The original image is edited to show what I meant to show last time.
I can propose you this formula:
[G2]=MIN([@[duration (weeks)]]*7,MAX(0,DATE(2026,1,1)-[@[start date]]))/7*[@[weekly cost]]