exceldateexcel-formuladate-range

How do I split a value between two years over a date range in Excel?


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.

See Example

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.


Solution

  • I can propose you this formula:

    enter image description here

    [G2]=MIN([@[duration (weeks)]]*7,MAX(0,DATE(2026,1,1)-[@[start date]]))/7*[@[weekly cost]]