asked on

Allocating days with a date range into years

Hi

I am trying to allocate days between a date range into years.

so

Start Date 16-2-2012 End Date 01/3/2015. There are 1109 days between this range.

I need to spread 319 days into 2012, 365 into 2013, 365 into 2014 and 60 days into 2015.

The file attached will do the allocation for 2012,2013,2014 but I need to add something to the formula so it puts the last 60 days into 2015.

I am assing a full year ends on the 31 December.

Is anyone able to help modify the formula in the file attahed.

Thanks for your help
EE-Spread-Formula.xlsx

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

1benjiman

ASKER

Thanks for your help

[ fanpages ]

Hi,

I have looked at the solution posted by Rgonzo1971, & have found some issues when the Start Date &/or End Date are changed.

Please look at the attached workbook.

Your initial row is still present (row #5).
Rgonzo1971's proposal is on row #6.

I have added rows #8 to #14.

Try changing row #6 to any of the Start Date/End Date combinations I have in rows #8 to #14 & you will see the "TOTAL" column I have added [T] that sums all the allocation values does not match the value in column [G].

However, my rows #8 to #14 are calculating the correct allocation values.

Please note that row #14 uses a random Start Date & a random Value so, with Automatic Calculation enabled, simply using function key [F9] will produce a further random Start Date & a random Value.

BFN,

fp.
Q-28145830.xlsx

[ fanpages ]

Thanks for your input, modus_operandi.

Obviously, with option #3, I would select my comment ID: 39216341 as the only solution.

This is not being biased; this is simply on the premise that the previously accepted solution is flawed.

[ fanpages ]

1benjiman has not returned, modus_operandi.

I guess we'll never know... :(