Solved

Allocating days with a date range into years

Posted on 2013-06-03
6
328 Views
Last Modified: 2013-10-09
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
0
Comment
Question by:1benjiman
  • 3
6 Comments
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39216108
Pls try this

=MAX(0,MIN($F$5,P4)-MAX($E$5,O4))

Regards
Copy-of-EE-Spread-Formula.xlsx
0
 

Author Closing Comment

by:1benjiman
ID: 39216161
Thanks for your help
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39216341
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
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39218214
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.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39559510
1benjiman has not returned, modus_operandi.

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now