Allocating Percentages by Project Year Worked

[powerhouse789]

I'm trying to get Excel to allocate how much % of a project will be completed in each budget year. It works sometimes but depending on the project start/end dates, it sometimes doesn't add up to 100%. Here is an image of how I have it setup and the formula I'm using in I7, which is the year it is having trouble in this example. I7 should equal 29.16667% so that the total percent in J7 adds up to 100%.

=ROUNDDOWN(MAX((EOMONTH(H$5,11)-H$5)+1-MAX($C$7-H$5,0)-MAX(EOMONTH(H$5,11)-$C$3,0),0)/((H$6-H$5)/12),1)/$B7

 

[cyrilbrd]

Try just =ROUND(MAX((EOMONTH(H$5,11)-H$5)+1-MAX($C$7-H$5,0)-MAX(EOMONTH(H$5,11)-$C$3,0),0)/((H$6-H$5)/12),1)/$B7

 

[powerhouse789]

Changing the beginning of the formula to "ROUND" instead of "ROUNDDOWN" as you suggested works in this particular instance, but not with other dates. I'm thinking the issue is with the # of days in a time period depending on the starting month. Is there a different formula besides EOMONTH that could help? I also tried YEARFRAC but get similar issues.

 

[cyrilbrd]

YEARFRAC DATEDIF and EOMONTH would give the same 364 365 or 366 days.

 

[bebo021999]

Book1
	A	B	C	D	E	F	G	H	I	J
1
2		Start	End
3		01/Sep/2021	31/Aug/2023
4				Y1	Y2	Y3	Y4	Y5	Y6
5				02/Jan/2018	01/Feb/2019	01/Feb/2020	01/Feb/2021	01/Feb/2022	01/Feb/2023	Total
6				31/Jan/2019	31/Jan/2020	31/Jan/2021	31/Jan/2022	31/Jan/2023	31/Jan/2024
7	Task 1	24	01/Sep/2021				20.96%	50.00%	29.04%	100.00%
8	Task 2	12	01/Sep/2021				41.92%	58.08%		100.00%
Sheet1

Cell Formulas
Range	Formula
E5:I5	E5	=D6+1
E6:I6	E6	=EDATE(D6,12)
D7:I8	D7	=IF(OR($C7>D$6,(EDATE($C7,$B7)-1)<D$5),"",(MIN(D$6,EDATE($C7,$B7)-1)-MEDIAN($C7,D$5,D$6)+1)/(MIN(EDATE($C7,$B7)-1,$C$3)-MAX($C7,$B$3)+1))
J7:J8	J7	=SUM(D7:I7)