Dynamic calculation based on individual start and end dates

[Cunner]

Hi folks,

I want to calculate budgets for an individual period based on given yearly budgets. It's probably best if you have a look at the example sheet.

In C3:C4, users can specify start and end dates for the period they want to consider. Cells B5:C8 contain the externally given budgets.
In cell C10 I would like to have an automated calculation that gives me a budget for the specified period. This budget should contain a linearly derived part of the yearly budget based.
In the given example one-half of 2015 and one quarter of 2017 as well as 2016 are included in the desired period. Accordingly the respective budget should be 325.

Does anyone have an easy solution for a formula for cell C10?

Thanks a lot and have a great new year.

[Beamernsw]

If the range will always span the 3 periods in the table, then try this:-

=DAYS360(C2,"31/12/"&YEAR(C2))*C6/360+C7+DAYS360("1/1/"&YEAR(C3),C3)*C8/360

[Cunner]

Unfortunately, the date range can be completely arbitrarily set and span any number of years.

[newdoverman]

Maybe this will work for you. I created a table in G2:H37 of years with arbitrary amounts for the budget of each year going to the year 2050.
First year calculation which calculates days of the year instead of months that can have a varying number of days.

=INDEX(H:H,MATCH(YEAR(C2),G:G,0))*((DATEDIF(C2,DATE(YEAR(C2)+1,MONTH(1),DAY(1)),"d"))/365)

Last year calculation:

=INDEX(H:H,MATCH(YEAR(C3),G:G,0))*(DATEDIF(DATE(YEAR(C3),MONTH(1),DAY(1)),C3,"d")/365)

Calculation of years between start and end

=SUMPRODUCT((G2:G37>--YEAR(C2))*(G2:G37<--YEAR(C3))*(H2:H37))

Total amount from Start to End

=INDEX(H:H,MATCH(YEAR(C3),G:G,0))*(DATEDIF(DATE(YEAR(C3),MONTH(1),DAY(1)),C3,"d")/365)+INDEX(H:H,MATCH(YEAR(C2),G:G,0))*((DATEDIF(C2,DATE(YEAR(C2)+1,MONTH(1),DAY(1)),"d"))/365)+SUMPRODUCT((G2:G37>--YEAR(C2))*(G2:G37<--YEAR(C3))*(H2:H37))

The workbook enclosed has the table created as well as the steps broken down and the final calculation. If you want to keep the steps you can simplify the total calculation by simply summing the steps that I have included.

The amount calculated works out to odd cents not equalling your round total but it is less than $1.00 difference.

[newdoverman]

To be absolutely certain, the table of budget values can be of any length spanning any number of years. Is that correct?

[Beamernsw]

Now THAT is why the forum has guru's :D

[Cunner]

Wow, thanks a lot. It works perfectly!
Just to understand it better: why do you put a "--" in front of the YEAR in the SUMPRODUCT formula?

EDIT: I changed the "365" in the denominator to a function that actually counts the days in a given year to accomodate for "Leap years" (is that how you say it?).

[newdoverman]

Leap years is correct.
The -- in the SUMPRODUCT isn't necessary in this case. It forces the value to be numeric. It was an error on my part.