I think it's a tough one...Calculating
Posted by DAVID COTE on October 17, 2001 8:08 PM
I have a start date (1st cell) and a finish date (2nd cell). I want to distribute the total number of days in each given year for a 5 year fixed period ?
Per example: Start date: Oct 17 2001 & Finish date: Sept 17 2003.
The results should be: 2001 = 75 days, 2002 = 365 days, 2003 = 260 days, 2004 = 0 and 2005 = 0.
What's the easy way of doing it ?
Thanks for your help !
Posted by Andrew Cheung on October 17, 2001 10:12 PM
David
In [b1]: input the begin date
In [b2]: input the end date
In [a5-a9]: input 0,1,2,3,4 repectively,
in [b5]: copy this formula to the cell and copy down to [b9] and you will get the required calculation.
FORMULA:
=IF(YEAR($B$1)+A5>YEAR($B$2),0,IF(YEAR($B$2)=YEAR($B$1),B2-B1,IF(YEAR($B$2)=YEAR($B$1)+$A5,$B$2-eomonth(TEXT(YEAR($B$2)&"/1/1","YYYY/MM/DD"),-1),IF(AND(YEAR($B$1)+A5>YEAR($B$1),YEAR($B$1)+A5<YEAR($B$2)),eomonth(TEXT(YEAR($B$1)+A5&"/12/1","YYYY/MM/DD"),0)-eomonth(TEXT(YEAR($B$1)+A5&"/1/1","YYYY/MM/DD"),-1),eomonth(TEXT(YEAR($B$1)+A5&"/12/1","YYYY/MM/DD"),0)-$B$1))))
Hope that this would help you, I have tested the formula and it works whenever you type the date.
But the end date MUST BE GREATER THAN the begin date.
Posted by Andrew Cheung on October 18, 2001 5:11 AM
David:
I think you have not start the required Add-Ins.
In Excel, choose "Tools", then "Add-Ins", and click on the "VBA Analysis Tools".
Then you could either re-start the Excel and re-open the file, or press "F2" in the formula cell and then press "Enter" again and you will find the formula works well as I could open and have the correct calculation you have entered
Posted by Mark W. on October 18, 2001 12:32 PM
It's not so tough... and BTW 2001 results should be 76...
With 10/17/2001 in cell A1 and 9/17/03 in cell A2...
enter {2001;2002;2003;2004;2005} into cells C1:C5.
Finally, enter the formula...
=SUM((YEAR(TREND($A$1:$A$2,{0;1}*($A$2-$A$1)+1,ROW(INDIRECT(1&":"&$A$2-$A$1+1))))=C1)+0)
into cell D1 and copy down to cell D5. That should do it!
