 # Need help with Excel formula

Hello-

I have the following worksheet attached with the following fields.

Tracking ID
Project date
Project value
Savings
Year
Project Length

I am trying to come up with a formula to calculate project value by how long the project is. For example if the project date is 1/1/2014, the project was for 2 years, and valued at \$100, the project value by year would be \$50 in 2014 and \$50 in 2015.

See attached file.
Microsoft ExcelMicrosoft ApplicationsMicrosoft Office Last Comment
barry houdini

8/22/2022 - Mon
NBVC

no attachment....
tawathav

byundt

If you don't need to pro-rate projects for partial years, you could use:
=AND(E2>=YEAR(B2),E2<=YEAR(B2)+F2-1)*C2/F2
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
byundt

If years need to be pro-rated because projects don't always begin on January 1, then the formula gets more complex:
=IF(YEAR(B2)=E2,(DATE(YEAR(B2)+1,1,1)-B2)/(365+(MOD(YEAR(B2),4)=0)),
IF(YEAR(DATE(YEAR(B2)+F2,MONTH(B2),DAY(B2))-1)=E2,(DATE(YEAR(B2)+F2,MONTH(B2),DAY(B2))-1-DATE(YEAR(B2)+F2-1,1,0))/(365+(MOD(E2,4)=0)),
AND(E2>=YEAR(B2),E2<=YEAR(B2)+F2-1)))*C2/F2